SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    752
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Question Finding Duplicate records

    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

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code:
    SELECT email_address
    FROM tbl_members
    GROUP BY email_address
    HAVING COUNT(*) > 1

  3. #3
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    752
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Hi


    Thanx for the prompt reply.

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


    Thnax

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cancer10 View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    752
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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
    Last edited by phantom007; Feb 26, 2009 at 22:50.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    752
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thank you so much r937 and guido2004.

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


    Many thanx


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •