Form calculating the total revenue for a product and the total numer of clients

[Giorgio]

hello everybody, I have a form that calculates the total revenue for a specific product and this is easy.


The query on which the form is based calculates the total quantities (in sqm, MetriQuadrati) and the revenue by summing up the orders and it is ok
It also calculates the total amounts paid and the VAT. Up to summing up the amounts of money, all is fine.

But, as a customer may have more than one order for the same product, when it comes to counting the number of customers, all the records are counted; so, for instance if I have 10 clients, but 12 orders (two clients ordered twice) the total number of customers displayed is 12 while it should be 10.
Even worse, if a client makes the order and then cancels it, the order is registered twice (one time when it comes in and one time when it is cancelled, with a minus sign) but the client is still counted twice.

Below is the SQL

SELECT DISTINCTROW ProdTable.ProdID, Sum(OrderTable.MetriQuadrati) AS [Somma Di MetriQuadrati], Sum(OrderTable.MetriQuadratiAltri) AS [Somma di MetriQuadratiAltri], OrderTable.ValutaFatturato, Sum(OrderTable.Fatturato) AS [Somma Di Fatturato], OrderTable.ValutaIVA, Sum(OrderTable.ImportoIVA) AS SommaDiImportoIVA, OrderTable.ValutaIncassato, Sum(OrderTable.Incassato) AS [Somma Di Incassato], Count(OrderTable.CodiceCliente) AS ConteggioDiCodiceCliente
FROM ProdTable INNER JOIN OrderTable ON ProdTable.ProdID = OrderTable.ProdID
GROUP BY ProdTable.ProdID, OrderTable.ValutaFatturato, OrderTable.ValutaIVA, OrderTable.ValutaIncassato, ProdTable.NomeCompleto
ORDER BY ProdTable.ProdID DESC;

Reading on the web I could not find a suitable solution, at least using SQL.

Any help?
Thanks in advance, giorgio

[CJ_London]

your need a separate query to get the number of customers - something like

SELECT ProdID, count(clientID) as Clients
FROM (SELECT DISTINCT ProdID, ClientID FROM OrderTable) Q

then join that to your group by query in another query on prodid

[CarlettoFed]

This is the one-query solution

SELECT qryRagg.ProdID, qryRagg.NomeCompleto, qryRagg.ValutaFatturato, Sum(qryRagg.SommaDiFatturato) AS TotFatturato, qryRagg.ValutaIVA, Sum(qryRagg.SommaDiImportoIVA) AS TotIva, qryRagg.ValutaIncassato, Sum(qryRagg.SommaDiIncassato) AS RorIncassato, Count(qryRagg.CodiceCliente) AS ContClienti
FROM (SELECT OrderTable.ValutaFatturato, OrderTable.ValutaIVA, OrderTable.ValutaIncassato, OrderTable.ProdID, ProdTable.NomeCompleto, OrderTable.CodiceCliente, Sum(OrderTable.Fatturato) AS SommaDiFatturato, Sum(OrderTable.ImportoIVA) AS SommaDiImportoIVA, Sum(OrderTable.Incassato) AS SommaDiIncassato
FROM OrderTable INNER JOIN ProdTable ON OrderTable.ProdID = ProdTable.ProdID
GROUP BY OrderTable.ValutaFatturato, OrderTable.ValutaIVA, OrderTable.ValutaIncassato, OrderTable.ProdID, ProdTable.NomeCompleto, OrderTable.CodiceCliente) 
AS qryRagg
GROUP BY qryRagg.ProdID, qryRagg.NomeCompleto, qryRagg.ValutaFatturato, qryRagg.SommaDiFatturato, qryRagg.ValutaIVA, qryRagg.SommaDiImportoIVA, qryRagg.ValutaIncassato, qryRagg.SommaDiIncassato
ORDER BY qryRagg.ProdID DESC;

otherwise you have to make two queries of which the first, which you can call qryRagg, with the following predicate

SELECT OrderTable.ValutaFatturato, OrderTable.ValutaIVA, OrderTable.ValutaIncassato, OrderTable.ProdID, ProdTable.NomeCompleto, OrderTable.CodiceCliente, Sum(OrderTable.Fatturato) AS SommaDiFatturato, Sum(OrderTable.ImportoIVA) AS SommaDiImportoIVA, Sum(OrderTable.Incassato) AS SommaDiIncassato
FROM OrderTable INNER JOIN ProdTable ON OrderTable.ProdID = ProdTable.ProdID
GROUP BY OrderTable.ValutaFatturato, OrderTable.ValutaIVA, OrderTable.ValutaIncassato, OrderTable.ProdID, ProdTable.NomeCompleto, OrderTable.CodiceCliente;

and the second with the following predicate

SELECT qryRagg.ProdID, qryRagg.NomeCompleto, qryRagg.ValutaFatturato, Sum(qryRagg.SommaDiFatturato) AS TotFatturato, qryRagg.ValutaIVA, Sum(qryRagg.SommaDiImportoIVA) AS TotIva, qryRagg.ValutaIncassato, Sum(qryRagg.SommaDiIncassato) AS RorIncassato, Count(qryRagg.CodiceCliente) AS ContClienti
FROM qryRagg
GROUP BY qryRagg.ProdID, qryRagg.NomeCompleto, qryRagg.ValutaFatturato, qryRagg.ValutaIVA, qryRagg.ValutaIncassato
ORDER BY qryRagg.ProdID DESC;

[Giorgio]

Thanks Carletto, sorry for the late reply as I do not get notifications of the replies.
But as far as I understand, the one-query solution implies anyway that I create another query, that you called qryRagg, am I wrong?

This is the one-query solution

SELECT qryRagg.ProdID, qryRagg.NomeCompleto, qryRagg.ValutaFatturato, Sum(qryRagg.SommaDiFatturato) AS TotFatturato, qryRagg.ValutaIVA, Sum(qryRagg.SommaDiImportoIVA) AS TotIva, qryRagg.ValutaIncassato, Sum(qryRagg.SommaDiIncassato) AS RorIncassato, Count(qryRagg.CodiceCliente) AS ContClienti
FROM (SELECT OrderTable.ValutaFatturato, OrderTable.ValutaIVA, OrderTable.ValutaIncassato, OrderTable.ProdID, ProdTable.NomeCompleto, OrderTable.CodiceCliente, Sum(OrderTable.Fatturato) AS SommaDiFatturato, Sum(OrderTable.ImportoIVA) AS SommaDiImportoIVA, Sum(OrderTable.Incassato) AS SommaDiIncassato
FROM OrderTable INNER JOIN ProdTable ON OrderTable.ProdID = ProdTable.ProdID
GROUP BY OrderTable.ValutaFatturato, OrderTable.ValutaIVA, OrderTable.ValutaIncassato, OrderTable.ProdID, ProdTable.NomeCompleto, OrderTable.CodiceCliente) 
AS qryRagg
GROUP BY qryRagg.ProdID, qryRagg.NomeCompleto, qryRagg.ValutaFatturato, qryRagg.SommaDiFatturato, qryRagg.ValutaIVA, qryRagg.SommaDiImportoIVA, qryRagg.ValutaIncassato, qryRagg.SommaDiIncassato
ORDER BY qryRagg.ProdID DESC;

otherwise you have to make two queries of which the first, which you can call qryRagg, with the following predicate

SELECT OrderTable.ValutaFatturato, OrderTable.ValutaIVA, OrderTable.ValutaIncassato, OrderTable.ProdID, ProdTable.NomeCompleto, OrderTable.CodiceCliente, Sum(OrderTable.Fatturato) AS SommaDiFatturato, Sum(OrderTable.ImportoIVA) AS SommaDiImportoIVA, Sum(OrderTable.Incassato) AS SommaDiIncassato
FROM OrderTable INNER JOIN ProdTable ON OrderTable.ProdID = ProdTable.ProdID
GROUP BY OrderTable.ValutaFatturato, OrderTable.ValutaIVA, OrderTable.ValutaIncassato, OrderTable.ProdID, ProdTable.NomeCompleto, OrderTable.CodiceCliente;

and the second with the following predicate

SELECT qryRagg.ProdID, qryRagg.NomeCompleto, qryRagg.ValutaFatturato, Sum(qryRagg.SommaDiFatturato) AS TotFatturato, qryRagg.ValutaIVA, Sum(qryRagg.SommaDiImportoIVA) AS TotIva, qryRagg.ValutaIncassato, Sum(qryRagg.SommaDiIncassato) AS RorIncassato, Count(qryRagg.CodiceCliente) AS ContClienti
FROM qryRagg
GROUP BY qryRagg.ProdID, qryRagg.NomeCompleto, qryRagg.ValutaFatturato, qryRagg.ValutaIVA, qryRagg.ValutaIncassato
ORDER BY qryRagg.ProdID DESC;