Count of specific records

[CodeMonkeyGirl]

I'm having problems constructing a query. I need to get a count of emails in my database, but only the emails that appear 2 or more times.
Can anyone help?

Thanks!
Justine

[rmiao]

Try

select count(email) total from Table group by email having count(email) > 1

[CodeMonkeyGirl]

Thanks so much! It worked beautifully!

-Justine

[Dan Chivers]

what is your approach to this script if you need to include a where line to limit the range in which you are evaluating the table for duplicates. Lets say, using the example that started the thread, I want to gather duplicates for e-mails, but only for those rows where the State field = 'MI'

How would that work with this script?

[Dan Chivers]

Never mind...I answered my own question...

select * from EmpAddresses where email in (select email from EmpAddresses group by email having count(email) > 1)
and state = 'MI'