I have a table with multiple customers. Each customer has a unique ID. Example, only one customer has ID as CustomerID = 100. The customer can be entered into the table numerous times but the CustomerID will be associated with a different ItemOrdered. The part of the table being questioned is, for example:

CustomerID              ItemOrdered

100                     ABCDE
100                     EFGHI
100                     JKLMN
200                     OPQRST
200                     UVWXY
300                     ZABCD

I have tried SUM, COUNT COUNT(DISTINCT ) and cannot get the correct answer from the query.

The correct answer from above would be

CustomerID 100  3
CustomerID 200  2
CustomerID 300  1

It is the 3, 2, 1 that I am after. Any help or point in the correct direction would be greatly appreciated.

closed as off topic by ypercubeᵀᴹ, dezso, Mark Storey-Smith, Erwin Brandstetter, Marian Feb 5 '13 at 15:26

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here. If this question can be reworded to fit the rules in the help center, please edit the question.

Select CustomerID,Count(*) as cnt
from Table
Group by CustomerID
  • Thank you bummi - it was so simple but so far. It worked just great. – Earl Davis Jan 31 '13 at 19:48

Not the answer you're looking for? Browse other questions tagged or ask your own question.