Top records by sum query 2

[fordtran]

I have about 1000 items in the Items table each with an ItemNo and ItemName. For each of these items I have placed many orders and have those ordered items in the OrderedItems table in which I have placed the ItemNo referring to the ID in the Items table and the quantity of that item ordered.

I now wish to query Access 2000 (Jet 4.0) to get the topmost 100 items ordered and order them by the total quantity ordered for each of those 100 items.

My query would read like this :

Select 100 items from the Items table for which the total of all quantities ordered in the ordereditems table grouped by itemno is the largest and order them by the quantities ordered.

Can someone please phrase this for me in proper SQL.

Thanks




fordtran

 

[PHV]

SELECT TOP 100 O.ItemNo, I.ItemName, Sum(O.quantity) AS Total
FROM Items AS I INNER JOIN OrderedItems AS O ON I.ItemNo = O.ItemNo
GROUP BY O.ItemNo, I.ItemName
ORDER BY 3 DESC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fordtran]

Are you from the space or nuclear research team ?
Thanks

fordtran

 

[fordtran]

I am using this sql successfully, but cannot add other fields from the orderitems table successfully.

S$ = "SELECT TOP 100 ordereditems.ItemNo, orderitems.ItemName, Sum(Ordereditems.quantity) AS Total FROM orderItems AS orderItems INNER JOIN OrderedItems AS Ordereditems ON orderitems.orderItemNo = Ordereditems.ItemNo GROUP BY Ordereditems.ItemNo, orderItems.ItemName ORDER BY 3 DESC"

I have orderitems.unit and orderitems.stock which appears in the orderitems table and which I wish to add to this sql.

Thanks

fordtran

 

[lespaul]

for every field that you include in the SELECT statement that is not an aggregate (SUM, COUNT, AVG), you MUST list that field in the GROUP BY clause:

"SELECT TOP 100 ordereditems.ItemNo, orderitems.ItemName, Orderitems.Unit, Orderitems.stock, Sum(Ordereditems.quantity) AS Total FROM orderItems AS orderItems INNER JOIN OrderedItems AS Ordereditems ON orderitems.orderItemNo = Ordereditems.ItemNo GROUP BY Ordereditems.ItemNo, orderItems.ItemName, Orderitems.Unit, Orderitems.stock ORDER BY 5 DESC"

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[fordtran]

Thanks you very much lespaul

fordtran