Single row wanted?

[milton747]

I was hoping for 1 row for each itemcode. I'm getting many.
Goal was-> For all clients, all orders...how much did we sell of each item?

? Any help welcome.


SELECT customers.custID,
orders.orderID,
items.itemcode,
( items.pricequote * items.quantity ) As LineValue
FROM customers, orders, items
WHERE
customers.custID = orders.custID
AND orders.orderID = items.orderID
GROUP BY customers.custID,
orders.orderID,
items.itemcode,
items.pricequote,
items.quantity
ORDER BY 4 DESC, items.itemcode, customers.custID

 

[PHV]

Perhaps this ?
SELECT C.custID, rderID, I.itemcode, Sum(I.pricequote * I.quantity) As LineValue
FROM (customers C
INNER JOIN orders O ON C.custID = O.custID)
INNER JOIN items I ON rderID = I.orderID
GROUP BY C.custID, rderID, I.itemcode
ORDER BY 4 DESC, 3, 1

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