Modify a duplicate lookup query

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

Modify a duplicate lookup query

Author

Topic

Topaz
Posting Yak Master

199 Posts

Posted - 2009-01-30 : 11:31:36

How would I edit the query below to make it display the 'duplicate email addresses' but select their website as well?

SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

JT

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-01-30 : 11:32:55

Not clear what you want. Could you please provide some sample data?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-30 : 11:41:20

Did you mean this?Not Sure.

SELECT websites,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY Website
HAVING ( COUNT(email) > 1 )

Topaz
Posting Yak Master

199 Posts

Posted - 2009-01-30 : 11:42:33

Well at the moment it brings up just the duplicate email addresses. Like this:

email Count
johnny@johnny.co.uk 3

but the actual record johnny@johnny.co.uk has a website attached to the same uniqueid 'www.johnny.co.uk'

So what I want to be displayed is this:

website email count
www.johnny.co.uk johnny@johnny.co.uk 3

JT

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-30 : 11:46:11

[code]SELECT website,email,
COUNT(*) AS NumOccurrences
FROM users
GROUP BY Website,email
HAVING ( COUNT(*) > 1 )[/code]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-01-30 : 11:47:58

Something like this?

select distinct u.website, t.email, t.NumOccurrences
from users u
join
(
SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING COUNT(email) > 1
) t
on u.email = t.email

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 11:50:38

may be this?

SELECT websites,email
COUNT(*) AS NumOccurrences
FROM users
GROUP BY Website,email
HAVING ( COUNT(*) > 1 )

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-30 : 11:52:15

quote:
Originally posted by visakh16

may be this?
SELECT websites,email
COUNT(*) AS NumOccurrences
FROM users
GROUP BY Website,email
HAVING ( COUNT(*) > 1 )

Hear an Echo on this one.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 11:54:16

or do you mean this?


select distinct website, email, NumOccurrences
from
(
select  u.website, t.email,
COUNT(email) OVER (PARTITION BY u.website) AS NumOccurrences
FROM users
) t
WHERE NumOccurrences>1

Topaz
Posting Yak Master

199 Posts

Posted - 2009-02-02 : 05:35:59

sorry to bring an old post up to the top but i'm currently using this query to find my 'duplicate websites'

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

This results in displaying the dupe sites with the 'NumOccurances' next to the website. Most of which say the website and then 2 occurances.

Obviously I want to keep one unique website and 'update the idstatus field to Duplicate' How would I go about doing this? I hope what I have explained makes sense?

Thanks

JT

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-02 : 05:50:36

once check by using count(distinct website)

Topaz
Posting Yak Master

199 Posts

Posted - 2009-02-02 : 05:58:45

hmm not sure what that means? I'm sure its the right answer but could you put it in some kind of 'query context'

much appreciated

JT

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-02 : 06:00:41

SELECT website, expressemail,
count(distinct website) AS NumOccurrences
FROM wce_contact
GROUP BY website, expressemail
HAVING ( count(distinct website) > 1 )

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-02-02 : 06:18:39

quote:
Originally posted by bklr

SELECT website, expressemail,
count(distinct website) AS NumOccurrences
FROM wce_contact
GROUP BY website, expressemail
HAVING ( count(distinct website) > 1 )

It will not return a single row.
Because count(distinct website) will always be 1 as you are grouping on website

Rahul Shinde

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-02 : 06:45:11

quote:
Originally posted by ra.shinde
It will not return a single row.
Because count(distinct website) will always be 1 as you are grouping on website

Rahul Shinde

yes it will not return single row
it will return the distinct count of records for website only

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-02-02 : 06:48:47

quote:
Originally posted by bklr

quote:
Originally posted by ra.shinde
It will not return a single row.
Because count(distinct website) will always be 1 as you are grouping on website

Rahul Shinde

yes it will not return single row
it will return the distinct count of records for website only

I mean to say this query will return zero rows

Rahul Shinde

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-02-02 : 06:54:39

Declare @wce_contact TABLE(website varchar(10), expressemail varchar(10))

insert into @wce_contact
select 'A','a'
union all
select 'A','b'
union all
select 'B','ab'
union all
select 'B','c'
union all
select 'B','ab'
union all
select 'C','d'

--------
SELECT website, expressemail,
count(distinct website) AS NumOccurrences
FROM @wce_contact
GROUP BY website, expressemail

--output
--website expressemail NumOccurrences
-- A a 1
-- B ab 1
-- A b 1
-- B c 1
-- C d 1

--------

SELECT website, expressemail,
count(distinct website) AS NumOccurrences
FROM @wce_contact
GROUP BY website, expressemail
HAVING ( count(distinct website) > 1 )

--output
--website expressemail NumOccurrences

----------------------

Rahul Shinde

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 08:44:32

quote:
Originally posted by Topaz

sorry to bring an old post up to the top but i'm currently using this query to find my 'duplicate websites'

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

This results in displaying the dupe sites with the 'NumOccurances' next to the website. Most of which say the website and then 2 occurances.

Obviously I want to keep one unique website and 'update the idstatus field to Duplicate' How would I go about doing this? I hope what I have explained makes sense?

Thanks

JT

nope..not fully. what about giving some sample data and o/p you want in below format?
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources