SQL Case Sensitive Group By

collation, sql, sql-server

Solution

Assign the COLLATE also on the GROUP BY clause

select accountCode COLLATE Latin1_General_CS_AS, count(OrderId)  
from <<TableName>>  
group by accountCode COLLATE Latin1_General_CS_AS

Problem

Intro I have a table which is having a column "AccountCode" and other columns. There are few account codes which are same but differs being case-sensitive. Example: ``` AccountCode OrderId ABC O001 ABC O002 abc O003 DEF O004 ``` Objective I want a query to have a group by on AccountCode column and get respective count. Expected output should be like ``` ABC 2 abc 1 DEF 1 ``` I have tried using COLLATE Latin1_General_CS_AS but unable to get desired result. Below query doesn't provide a case-sensitive output ``` select accountCode COLLATE Latin1_General_CS_AS, count(OrderId) from <<TableName>> group by accountCode ```

Original source