Building sql query with count() where count() is > 1

Question

If I have a table where there are duplicate IDs, how can I count the number of times the same ID appears in the table and only show records that have a count greater than 1?

I've tried:

SELECT COUNT(ID) AS myCount FROM myTbl
WHERE myCount > 1 GROUP BY ID

But it says myCount is invalid column name. Can someone show me what I'm doing wrong?

Answer 1

You need to use the HAVING keyword:

SELECT COUNT(ID) AS myCount FROM myTbl
GROUP BY ID
HAVING COUNT(ID) > 1

From MSDN:

Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.