Query Analyzer Count(field)

[JoJo]

I want to run a query that will only return the records where the count
for a specific value is greater than 1.

So test data is:

A9103
A9103
A9105
A9108
A9109

If I ran a query against it would only return the 2 records with the
value A9103.

How is that done?

Here is my test attempt:

SELECT AGENCY_PROFILE_ID
FROM oACES_impactHEADERdata
WHERE COUNT(AGENCY_PROFILE_ID) > 1
ORDER BY AGENCY_PROFILE_ID

[Fábio Pinheiro]

select column from table group by column having count(*) > 1

JoJo escreveu:

[Fábio Pinheiro]

SELECT AGENCY_PROFILE_ID
FROM oACES_impactHEADERdata
GROUP BY AGENCY_PROFILE_ID HAVING COUNT(AGENCY_PROFILE_ID) > 1 
ORDER BY AGENCY_PROFILE_ID

JoJo escreveu:

[Gregory Grater]

SELECT AGENCY_PROFILE_ID, COUNT(*)

FROM oACES_impactHEADERdata
GROUP BY AGENCY_PROFILE_ID

HAVING COUNT(*) > 1
ORDER BY AGENCY_PROFILE_ID