Cannot Group By UNIQUEIDENTIFER datatype

• Express12

  SSCoach

  Points: 18514

  More actions

  July 27, 2006 at 9:41 am

  #191591

  In search of suggestion to rewrite SQL in order to resolve Error Msg returned in SQL 2000 Q/A:

  SELECT Col_1, COUNT(COL_1) FROM TAB_X GROUP BY COL_1 HAVING COUNT(*) > 1

  ((( Column attributes are:     COL_1  

  UNIQUEIDENTIFIER NOT NULL  )))

  Returns Error Msg:

  Msg 409, Level 16, State 2, Line 1  The count aggregate operation cannot take a uniqueidentifier data type as an argument.

  BT

• Pam Brisjar

  SSChampion

  Points: 12094

  More actions

  July 27, 2006 at 9:46 am

  #651921

  Well, that woirks for me in 2005.  Unfortunately I don't have 2000 to test there.

  Try this, though:

  SELECT Col_1, COUNT(1) FROM TAB_X GROUP BY COL_1 HAVING COUNT(*) > 1

• WesleyB

  Ten Centuries

  Points: 1307

  More actions

  July 28, 2006 at 1:03 pm

  #652193

  Try to CAST it to a CHAR.

  SELECT Col_1, COUNT(COL_1) FROM TAB_X GROUP BY CAST(COL_1 as CHAR(36)) HAVING COUNT(*) > 1

   

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  July 30, 2006 at 6:37 pm

  #652262

  If it's not a sectret, what's the point?

  _____________
  Code for TallyGenerator

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  July 30, 2006 at 6:56 pm

  #652263

  FYI

  For NOT NULL coluns COUNT(Column) and COUNT(*) will ALWAYS return the same result.

  Run this and feel lucky:

  SELECT Col_1, COUNT(*) FROM TAB_X GROUP BY COL_1 HAVING COUNT(*) > 1

  _____________
  Code for TallyGenerator