Vikas Vaidya Vikas Vaidya - 1 month ago 8
SQL Question

SQL Case Sensitive Group By

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

Answer

Assign the COLLATE on the GROUP BY clause

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