Alex Rivera | Logout

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

Asked 2012-01-05T22:26:36.453
10

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?

sql-server t-sql
Edit
Report

1 Answer

19

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.

answered 2012-01-05T22:28:05.963

Your Answer