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? I am using SQL Server 2000.

Thank you.

 

[maswien]

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 email in 
 (
 select email
 from  users 
 where  user_id > 7146375
 group by email
 HAVING COUNT(email) > 1
 )

 

[jbenson001]

Are you trying to find duplicate email address for each user, or just a count of how many they have, which is what your code is doing?

 

[Catadmin]

Does it give you any error message or just return NULL results?

And are all your fields in the same table or are you trying to pull fields from another table?





Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[jbenson001]

Sorry, ignore my last post. The code looks correct. I have tested it with a quick table I created. Are you getting any errors?

 

[WannaLearn]

I am not getting any errors. I get no results at all. If I take out the extra fieldnames then it returns one email and shows it appears 2x.

What I need is:
1) any/all email address and the number of times it appears in the table more then once.

To answer jbenson001 I am tring to find all duplicate emails for every user_id.

 

[WannaLearn]

I just tried maswien's idea but got errors stating that the 'group by' was missing, so I added that in:

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 email in 
	 (
		 select email
		 from  users 
		 where  user_id > 7146375
		 group by email
		 HAVING COUNT(email) > 1
	 )
group by user_id, email, FirstName, LastName, Address, Phone, city, state, zip, country, BirthMonth, BirthDay, 
	 BirthYear, Gender, IPAddress, gift

However, while this returns what I want it makes each email a record of its own. If I have three email appear then it shows emailcount as 1 and not 3. I'm guessing that cause of the 'group by' clause.

 

[jbenson001]

The group by is probably your problem. For your original code to work, or maswien's code, the values in each of the additional columns must be identicle on each row. If one is different, it will break it out to a spearate row. I suspect this is the problem since you are getting 3 rows instead of 1.

 

[maswien]

Wannalearn, Looks like you didn't try my code, the reason your code doesn't return email is that there is no duplicate records in your table, so the having count() > 1 won't return results, what you need to find are not the duplicate rows but duplicate email address, so first get the duplicate email address and then map them into the whole records.

 

[maswien]

I'm sorry, I just copied part of your code, it's wrong and it should be:

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 email in 
     (
         select email
         from  users 
         where  user_id > 7146375
         group by email
         HAVING COUNT(email) > 1
     )

 

[WannaLearn]

Ok, so I did this:

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

And get back this:

EMAIL			EMAILCOUNT
a@yahoo.com		2
b@NSO.UCHC.EDU		5
c@hotmail.com		2
d@aol.com		2
e@yahoo.com		2
f@maktoob.com		2
g@aol.com		2
h@ADVANTEXMAIL.NET	2
i@yahoo.com.sg		3
j@earthlink.net		4
k@earthlink.net		2

How can I modify the query to have all the fields that I need?

 

[jbenson001]

Take maswien's suggestion. Use your code above and pull in the ID as well. Then join back to your table to get the other columns you need.

 

[maswien]

Still has typo, today is definate not my day :-(

should be:

select    user_id, email, FirstName, LastName, 
          Address, Phone, city, state, zip, 
          country, BirthMonth, BirthDay, BirthYear, 
          Gender, IPAddress, gift
from     users where email in 
     (
         select email
         from  users 
         where  user_id > 7146375
         group by email
         HAVING COUNT(email) > 1
     )

 

[WannaLearn]

maswien, your last query was perfect!

I still need to test it, which i will do on MOnday now.

Thanks.

 

[r937]

oh my goodness, i wish i had seen this thread before doing all the work myself in this other thread -- thread232-1075082

please, don't cross-post!!

none of the queries in this thread correctly returns the emailcount in the SELECT like mine does

also, it is possible for there to be users with one of the duplicated emails (as determined by the subquery with HAVING) which are not > 7146375, and therefore that condition should also be in the outer query's WHERE clause

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