Post reply

Cannot Group By UNIQUEIDENTIFER datatype

  • 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

  • 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

  • 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

     

  • July 30, 2006 at 6:37 pm

    #652262

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

    _____________
    Code for TallyGenerator

  • 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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply