Aggregates

[milton747]

Hi folks,

I added a clause to a previously working SQL. (Provided kindly form this forum)
(See below for complete good and bad verisons. )
The good version inserts the OrderTotal in each row:


(SELECT SUM ( items.quantity * items.pricequote )
FROM items
WHERE items.orderID = orders.orderID
AND customers.custID = orders.custID
)
AS OrderTotal,



That worls good. So I inserted a cluase to get the total for all orders for each customer. As follows:


(SELECT SUM ( items.quantity * items.pricequote )
FROM items
WHERE items.orderID = orders.orderID
AND customers.custID = orders.custID
GROUP BY customers.custID
)
AS CustomerTotal,


It runs, however, CustomerTotal is the same as OrderTotal.

Any suggestions how to get it to show the customer total.
And while I'm asking, how about a GrandTotal for all rows? (So I can do % calculations)


Big thanks
Milton.

Full SQL Below...


** THIS WORKS ---------------------- begin

SELECT customers.custID, orders.orderID, items.quantity, items.pricequote, items.itemcode, items.orderID,
( items.quantity * items.pricequote ) AS LineTotal,


(SELECT SUM ( items.quantity * items.pricequote )
FROM items
WHERE items.orderID = orders.orderID
AND customers.custID = orders.custID
)
AS OrderTotal,

LineTotal / OrderTotal * 100 AS Percentage


FROM customers, orders, items
WHERE customers.custID = orders.custID AND orders.orderID = items.orderID
AND customers.custID < CID

ORDER BY
customers.custID,
orders.orderID,
7 DESC

** THIS WORKS -------- End





** THIS RUNS -- But wrong anwer -------- begin

SELECT customers.custID, orders.orderID, items.quantity, items.pricequote, items.itemcode, items.orderID,
( items.quantity * items.pricequote ) AS LineTotal,


(SELECT SUM ( items.quantity * items.pricequote )
FROM items
WHERE items.orderID = orders.orderID
AND customers.custID = orders.custID
)
AS OrderTotal,


(SELECT SUM ( items.quantity * items.pricequote )
FROM items
WHERE items.orderID = orders.orderID
AND customers.custID = orders.custID
GROUP BY customers.custID
)
AS CustomerTotal,

LineTotal / OrderTotal * 100 AS Percentage



FROM customers, orders, items
WHERE customers.custID = orders.custID AND orders.orderID = items.orderID
AND customers.custID < CID

ORDER BY
customers.custID,
orders.orderID,
7 DESC


--- End

 

[AlexCuse]

For customer total, remove the first condition from your where clause:

(SELECT SUM ( items.quantity * items.pricequote ) 
 FROM items 
WHERE [s]items.orderID  =  orders.orderID
AND [/s]customers.custID = orders.custID
[s]GROUP BY customers.custID[/s]
) 
AS CustomerTotal,

For grand total, remove all the where clause.

Your GROUP BY is unnecessary in this case, but I would suggest writing a query that would use a group by to show all of your totals, then joining to that (rather than using an inline subquery that will need to execute once per row). Let's make sure the query is working first though.

Hope this helps,

Alex



I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[milton747]

Hi Alex,

Thanks for reply.
I agree the SQL below looks good, and I tried it first:

(SELECT SUM ( items.quantity * items.pricequote )
FROM items WHERE customers.custID = orders.custID
) AS CustomerTotal,

It reutrns a huge number. And oddly, the grand total SQL below returns the same number as CustomerTotal.

(SELECT SUM ( items.quantity * items.pricequote )
FROM items ) AS GrandTotal

Strange.
I'll play around, but any input welcome.

Milton.

-------------------------------------


* CORRECT

(SELECT SUM ( items.quantity * items.pricequote )
FROM items
WHERE items.orderID = orders.orderID
AND customers.custID = orders.custID
) AS OrderTotal,



* WRONG

(SELECT SUM ( items.quantity * items.pricequote )
FROM items
WHERE customers.custID = orders.custID
) AS CustomerTotal,

* WRONG - same as CustomerTotal !?!?!

(SELECT SUM ( items.quantity * items.pricequote )
FROM items
) AS GrandTotal


-eom-

 

[AlexCuse]

Upon further review, I don't think this is doing anything either:

AND customers.custID = orders.custID

I suspect if you remove this from your order total subquery, you will still get the same number.

You will need to join from items to orders or customers in order to have custID available within your subquery to do any comparisons.

Hope this helps

Alex



I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom