Add a simple line - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Add a simple line

Author

Topic

Topaz
Posting Yak Master

199 Posts

Posted - 2009-03-02 : 06:07:15

I'm currently using this query to find the duplicate email addresses in my database.

SELECT expressemail,
COUNT(expressemail) AS NumOccurrences
FROM wce_contact
GROUP BY expressemail
HAVING ( COUNT(expressemail) > 1 )

All good and well but what i want to add is a line saying 'find the dupes (above query) where idstatus is null'

Not sure where to add it...

JT

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-02 : 06:12:17

SELECT expressemail,
COUNT(expressemail) AS NumOccurrences
FROM wce_contact
where idstatus is null
GROUP BY expressemail
HAVING ( COUNT(expressemail) > 1 )

Topaz
Posting Yak Master

199 Posts

Posted - 2009-03-02 : 06:25:35

hmm very simple.

Thank you very much!

JT

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-02 : 06:45:45

quote:
Originally posted by Topaz

hmm very simple.

Thank you very much!

JT

welcome

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-02 : 09:04:30

quote:
Originally posted by bklr

SELECT expressemail,
COUNT(expressemail) AS NumOccurrences
FROM wce_contact
where idstatus is null
GROUP BY expressemail
HAVING ( COUNT(expressemail) > 1 )

this will always return count as 1 as you're grouping by expressemail and taking count of it. probably what OP wanted is this


SELECT expressemail,
COUNT(*) AS NumOccurrences
FROM wce_contact
where idstatus is null
GROUP BY expressemail
HAVING ( COUNT(*) > 1 )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources