How do you query for number of occurences?

karjagis · January 27th, 2005, 07:15 AM

My question is the following:
Consider the table:

ITEM WHOLESALE WHOLESALE+0.15
-------------- --------- --------------
TOMATOES .34 .49
TOMATOES .51 .66
BANANAS .67 .82
TURNIPS .45 .60
BANANAS .89 1.04
APPLES .23 .38

I need a query which retrieves only those âITEMâs whose number of occurrences is more than one.

That is, if I run the query, I should get only TOMATOES and BANANAS and not the other things which have occurred only once.

Can you think it for me?

shahchi1 · January 27th, 2005, 10:32 AM

SELECT Item, Count(*) As TotalItem
FROM MyTable
Group By Item
Having Count(*) > 1