• Raghu,

    The results you are getting are due to you only specifying the collation on the where clause.

    The query is selecting rows 1, 3 and 6 since they are the rows that match when the case sensitive collation is specified.

    However the Group By clause does not specifiy Case Sensitive and so the results are grouped case insensitive, combining the AB and aB.

    You would need the code below to get your expected result (assuming the count next to AB was meant to be 1)

    SELECT COL2 COLLATE SQL_Latin1_General_CP1_CS_AS, COUNT(COL2)

    FROM testQOTD

    WHERE col2 COLLATE SQL_Latin1_General_CP1_CS_AS IN ( 'AB', 'aB', 'xy' )

    GROUP BY COL2 COLLATE SQL_Latin1_General_CP1_CS_AS