Count duplication based on role

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

SQL Server 2005 Forums

Transact-SQL (2005)

Count duplication based on role

Author

Topic

xrum
Yak Posting Veteran

87 Posts

Posted - 2010-06-23 : 11:16:44

hi,

i need to count duplicate emails from table1 for users who are only in the role "members" in Table2.

this is what i have so far, but it's not working :( please help

select count(t1.email) as MyCount, t1.email from Table1 t1, Table2 t2 group by t1.email where t1.userid = t2.userid and t2.roleid="members" having count(t1.email) >1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-23 : 12:48:12

put your WHERE before GROUP BY

No, you're never too old to Yak'n'Roll if you're too young to die.

xrum
Yak Posting Veteran

87 Posts

Posted - 2010-06-23 : 13:07:25

hm. that worked but it gives me wrong results.

for example. it tells me there are 3 aasdf@asdf.com but there are only two in Table1

Sachin.Nand

2937 Posts

Posted - 2010-06-23 : 14:34:59

Try this


select count(distinct t1.email) as MyCount, t1.email from Table1 t1, Table2 t2 where t1.userid = t2.userid and t2.roleid="members" group by t1.email and  having count(distinct t1.email) >1

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

xrum
Yak Posting Veteran

87 Posts

Posted - 2010-06-23 : 15:56:19

thanks

Sachin.Nand

2937 Posts

Posted - 2010-06-24 : 00:15:12