List:General Discussion« Previous MessageNext Message »
From:Visolve DB TEAM Date:August 31 2006 6:10am
Subject:Re: Group By question
View as plain text  
Hello ,
Another one alternate solution without join 

SELECT  id,count(email) as No ,email FROM email GROUP BY   email having count(email)>1

Thanks 
Visolve DB Team.
  ----- Original Message ----- 
  From: Peter Brawley 
  To: Chris W 
  Cc: MYSQL General List 
  Sent: Thursday, August 31, 2006 3:02 AM
  Subject: Re: Group By question


  Chris,

  >I would like to query all rows that have more 
  >than one person with the same email address. 

  select id,count(emailaddr) as howmany
  from tbl t1 join tbl t2 using(emailaddr)
  group by id 
  having howmany>1;

  PB

  -----

  Chris W wrote: 
    I have a table of people with one of the fields being the email address.  I would like
to query all rows that have more than one person with the same email address.   For
example if the data were like this... 

    A 123@stripped 
    B 456@stripped 
    C 984@stripped 
    D 123@stripped 
    E 456@stripped 
    F 321@stripped 

    The query would return row A, D, B, and E,  in that order.  It would not return C or F





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


  No virus found in this outgoing message.
  Checked by AVG Free Edition.
  Version: 7.1.405 / Virus Database: 268.11.7/433 - Release Date: 8/30/2006




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



  -- 
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
Thread
Group By questionChris W30 Aug
  • Re: Group By questionPeter Brawley30 Aug
  • Re: Group By questionVisolve DB TEAM31 Aug
  • Re: Group By questionmizioumt31 Aug
  • changing datadirHelen M Hudson4 Sep