count rows with same id

• Berth

  Say Hey Kid

  Points: 691

  More actions

  March 16, 2009 at 5:41 am

  #198018

  Hello,

  I want to count rows with same id but I am not really shure about the syntax.

  table x

  rowid id color

  1 134 red

  2 134 red

  3 134 red

  4 6 red

  I then want to count rows with the same id so the output would be:

  id rowamount color

  134 3 red

  4 1 red

  I think there must be a HAVING clause involved somewhere, HAVING(COUNT(rowamount)), but I am not really shure how to do this.

  Regards

• steveb.

  SSC-Forever

  Points: 46733

  More actions

  March 16, 2009 at 5:43 am

  #960130

  try

  select id, count(*) as RowAmount, color

  from tableX

  Group By id, color

• Christopher Stobbs

  SSC-Insane

  Points: 21098

  More actions

  March 16, 2009 at 5:44 am

  #960131

  Why do you want a having clause?

  Is it because you only want to see rows with a duplicate id?

  A Having clause is basically like a where clause but it happens after the GROUP BY .

  so you could have something like

  HAVING COUNT(id) > 1 would mean there would need to more than 1 instance of the id.

  ----------------------------------------------
  Try to learn something about everything and everything about something. - Thomas Henry Huxley

  :w00t:
  Posting Best Practices[/url]
  Numbers / Tally Tables[/url]

  SQL-4-Life