Select Customers who have ordered more than a certain number of items

[Kliot]

I want to find out what customers have ordered more than a certain number of a particular item.

I can easliy write a select query that will give me the sum of the item sold to customers but I can't figure out how to select the customers that have ordered more than a certain number.

for example I want to find out what customers have placed orders totaling more 50 widgets.

Thanks
Perrin

 

[ousoonerjoe]

This can also used to find dupe records. Change 50 to 1.

SELECT CustID, COUNT(CustID) AS NumCount 
FROM OrderTable 
GROUP BY CustId
HAVING (COUNT(CustID) > 50) 
ORDER BY CustID;

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[Kliot]

ousoonerjo,

Thanks that's exactly what I was trying to figure out. Now it gets a little more complicated. Now I want to find out what customers have ordered more than 50 widgets or more than 50 woodles.

If I do a sum in the having block it will give me the result for who has ordered a total of 50 or more widgets and woodles. So if a customer ordered 25 widgets and 25 woodles they would show up in the result and this is not what I want.

Any suggestions.
Thanks

 

[ousoonerjoe]

Change ProdType accordingly.

SELECT CustID, COUNT(CustID) AS NumCount, 'Widget' As ProductType
FROM OrderTable
WHERE ProdType = 'Widget'
GROUP BY CustID
HAVING (COUNT(CustID) > 50) 

UNION ALL

SELECT CustID, COUNT(CustID) AS NumCount, 'Woodles' As ProductType
FROM OrderTable
WHERE ProdType = 'Woodles'
GROUP BY CustID
HAVING (COUNT(CustID) > 50) 
ORDER BY CustID

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.