Group by, having count

• Simon Parry

  Ten Centuries

  Points: 1297

  More actions

  November 24, 2010 at 9:32 am

  #229055

  Hi

  Could someone help me with a query i am having?

  i am trying to select all the records in table which have duplicate post codes

  i can do this ok by using

  select s_post_code

  from address

  group by s_post_code having count(s_post_code)>1

  i want to include an extra field (company_name) into the query

  something like

  select s_post_code , company_name

  from address

  group by s_post_code, company having count(s_post_code)>1

  but this returns nothing, as the company names are potentially different, i think i need to do this as a nested query but am struggling a bit, can anyone give me any pointers

  thanks

  Simon

• Robert Klimes

  SSCoach

  Points: 17865

  More actions

  November 24, 2010 at 9:48 am

  #1255175

  you could also use a subquery http://msdn.microsoft.com/en-us/library/aa213252%28SQL.80%29.aspx

  select company_name,s_post_code

  from address

  where s_post_code in

  (select s_post_code

  from address

  group by s_post_code having count(s_post_code)>1)

  Bob
  -----------------------------------------------------------------------------
  How to post to get the best help[/url]

• Simon Parry

  Ten Centuries

  Points: 1297

  More actions

  November 24, 2010 at 9:49 am

  #1255177

  thanks that helps