issues with "having"

[WannaLearn]

I want to find all email addresses in my users table that exist more than once, this works:

select   email, count(email) as emailcount
from   	 users 
where  	 user_id > 7146375
group by email
HAVING 	 COUNT(email) > 1

The above code brings back one email and shows that it appered twice.

Now, when I try to add more fields to the select clause, I get back no results. I added:

select   user_id, email, count(email) as emailcount, FirstName, LastName, Address, Phone, City, State, Zip, 
	 	 Country, BirthMonth, BirthDay, BirthYear, Gender, IPAddress, gift
from   	 users 
where  	 user_id > 7146375
group by user_id, email, FirstName, LastName, Address, Phone, City, State, Zip, 
	 	 country, BirthMonth, BirthDay, BirthYear, Gender, IPAddress, gift
HAVING 	 COUNT(email) > 1

So how can I get this to work with adding more fields? And why doesn't it work with those new fields added?

Thank you.

 

[WannaLearn]

Forgot to mention I am using SQL Server 2000

 

[r937]

the reason you are not getting results is because there is no combination of user_id, email, FirstName, LastName, Address, Phone, City, State, Zip, country, BirthMonth, BirthDay, BirthYear, Gender, IPAddress, gift that occurs more than once

that's what GROUP BY does, it groups on combinations of values

what i think you want is the complete details for any users which have an email that occurs more than once

that's a subquery

select user_id
     , email
     , ( select count(email)
           from users 
          where user_id > 7146375 
            and email = foo.email ) 
         as emailcount
     , FirstName
     , LastName
     , Address
     , Phone
     , City
     , State
     , Zip
     , Country
     , BirthMonth
     , BirthDay
     , BirthYear
     , Gender
     , IPAddress
     , gift
  from users as foo
 where user_id > 7146375
   and email in
     ( select email
         from users 
        where user_id > 7146375
       group by email
       having count(email) > 1 )

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)