One Row Wanted.

[milton747]

I'm the reports guy, so I struggle with SQL a bit.

Here's my jam: For a particular customer, for all their orders, get the total quantity of each item
they buy, and order big to small. (So we know what they buy most of.)

Here' s what I have, but each order is separate. The ORDER BY 3 DESC works, but only within separate orders, rather than 1 row for each item for all orders.

Thanks for any help.

SELECT items.itemcode, items.quantity,
( Sum( items.quantity) ) AS CC,
customers.custID,
orders.orderID
FROM customers, orders, items
WHERE
customers.custID = orders.custID
AND orders.orderID = items.orderID
AND customers.custID = CID
GROUP BY orders.orderID, items.quantity, items.pricequote, items.itemcode, customers.custID
ORDER BY 3 DESC;



-- eom ---

 

[PHV]

And what about this ?
SELECT I.itemcode, Sum(I.quantity) AS CC, C.custID
FROM (customers C
INNER JOIN orders O ON C.custID = O.custID)
INNER JOIN items I ON rderID = I.orderID
WHERE C.custID = CID
GROUP BY I.itemcode, C.custID
ORDER BY 2 DESC;

BTW didn't I already gave you an answer to your very similar question here ?
thread701-1369452
Why didn't you answered it ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[milton747]

May 21st.

Hi PHV,
Thanks for SQL. I just tested it and it works a treat

The other SQL you recall discovered the top selling items corporate wide (across all BUs and clients). I was working on that report over the weekend.

Huge Thanks Again.
Milton.