SQL help please

[Visitor]

I've got a query that I just can't seem to get right.

I have a table (orders) with itemName and itemQty. Every order makes a
new row so I can have many rows with the same itemName plus the
itemQty that was ordered.

What i'm trying to do is get how the total number of each item
ordered.

First try:

SELECT itemName, count(*) as popitemCount
FROM Orders
Group By itemName
ORDER BY Count(*) DESC

Looks good except it only tells me how many times each item was
ordered regardless of itemQty, not how many total of each item.

I need something like..

SELECT itemName * itemQty, count(*) as popitemCount
FROM Orders
Group By itemName
ORDER BY Count(*) DESC

-Of course the above does not work but you get the idea.

This seems so simple, what am i missing? Any suggestions would be
appreciated greatly.

-Johnny B

 

[Andy Hull]

Hi

For the total quantity ordered use...

SELECT itemName, sum(itemQty) as popitemCount
FROM Orders
Group By itemName
ORDER BY sum(itemQty) DESC


I'm pretty sure that the above is what you want but use the below SQL if you
want
to see how many orders there were for each quantity of each item...

SELECT itemName, itemQty, count(*) as popitemCount
FROM Orders
Group By itemName, itemQty
ORDER BY Count(*) DESC


hth

Andy Hull