Finding Duplicate records

[cancer10]

HI

I have a table called tbl_members with 5,000 records

I have 5 colums, one of which is email_address


Is there anyway I can find out duplicate email addresses in the email_address colum?


Thanx

[guido2004]

SELECT email_address
FROM tbl_members
GROUP BY email_address
HAVING COUNT(*) > 1

[cancer10]

Hi


Thanx for the prompt reply.

Does not GROUP BY needs an aggregate function like sum(), avg() etc?


Thnax

[r937]

Does not GROUP BY needs an aggregate function like sum(), avg() etc?

no, it doesn't

the purpose of GROUP BY is to aggregate, i.e. to produce a single group result row from multiple individual detail rows

there is no requirement to put an aggregate function into the SELECT clause (although it is very common to do so)

notice that in the example given, there actually is an aggregate function -- it's in the HAVING clause

[cancer10]

EDIT:

Sorry there is a little change in my question now. Sorry for bothering you.


I have a table called tbl_members with 5,000 records

I have 3 colums,

memberID
member_name
email_address

I want to group all members who have similar email address. The select clause should also include memberID and member_name.

Can you help me setting up a query for this please?

Thanx

[r937]

SELECT t.memberID
     , t.member_name
     , t.email_address
  FROM ( 
       SELECT email_address
         FROM tbl_members
       GROUP 
           BY email_address
       HAVING COUNT(*) > 1
       ) AS d
INNER
  JOIN tbl_members AS t
    ON t.email_address = d.email_address

[cancer10]

Thank you so much r937 and guido2004.

You have done a great job for me. very helpful.


Many thanx