problem with group by

• daomingsi

  Grasshopper

  Points: 11

  More actions

  April 17, 2005 at 10:26 pm

  #66035

  I want to query all the columns of a table after doing a group by of a column and with a condition that the number of record in a column is more than 1.

  example:

  This is the table:

  C1 C2 C3

  1   0   4

  1   0   5

  2   1   5

  I want to query all 3 columns (C1, C2, C3)  if the C1 column has at least one same record.  So, I want the result set to display row 1 and row2 only with all the 3 columns.

  I tried this:

  select C1, C2, C3 from ex1

  group by  C1, C2, C3

  having count(C1) >1

  But it doesn't work, it gives me 0 row.  I don't understand.  Please help

  Thanks

   

• Dinesh Asanka

  SSChampion

  Points: 11058

  More actions

  April 17, 2005 at 10:51 pm

  #553073

  yes it should give zero records. as you are grouping them by c1,c2,c3. in your query count(c1) is 1

  check this

  select count(c1), C1, C2, C3 from ex1 group by  C1, C2, C3 having count(C1) >0

  try this

  select * from ex1 where c1 in(

  select c1 from ex1 group by c1 having count(c1) > 1)

   

  
  
  
  

  My Blog: http://dineshasanka.spaces.live.com/