union all, group by???

• DuruG

  SSCrazy

  Points: 2089

  More actions

  October 30, 2002 at 7:00 am

  #79250

  Hi everybody!

  I have 5 columns in a table that contains the same type of values (the last 5 in the example). Let have this example:

  1 R1 C1 T1 U1 K1

  2 C1 U1 T1 M1 R1

  3 R1 U1 C1 M1 K1

  My final goal is to show how many times each value occured.

  Like:

  R1 3

  C1 3

  U1 3

  M1 2

  K1 2

  T1 2

  Any ideas?

  Thanks a lot,

  Durug

• Andy Jones, DBA

  Hall of Fame

  Points: 3641

  More actions

  October 30, 2002 at 7:51 am

  #439807

  Hi, try,

  select

  c

  ,count(c)

  from

  (

  select c1 as c from a

  union allselect c2 from a

  union allselect c3 from a

  union allselect c4 from a

  union allselect c5 from a

  ) t

  group by c

  This is for a table called [a] with columns c1,c2,c3,c4 and c5.

  Regards,

  Andy Jones

  Edited by - andyj93 on 10/30/2002 07:52:04 AM

  .

• DuruG

  SSCrazy

  Points: 2089

  More actions

  October 30, 2002 at 8:01 am

  #439815

  Thanks a lot!

  I was close to your solution but I was missing the alias t for the union select statement.

  And I was out of solutions.

  Really appreciate.

  Durug