CODEWITHSUNDEEP
sqlsql-servercollation
Vikas Vaidya
Vikas Vaidya

Reputation: 368

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

Upvotes: 3

Views: 5037

Answers (3)

Marc Guillot
Marc Guillot

Reputation: 6455

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

Upvotes: 4

Mansoor
Mansoor

Reputation: 4192

BEGIN TRAN CREATE TABLE #Temp(AccountCode VARCHAR(100),OrderId VARCHAR(100))

INSERT INTO #Temp(AccountCode ,OrderId) SELECT 'ABC','O001' UNION ALL SELECT 'ABC','O002' UNION ALL SELECT 'abc','O003' UNION ALL SELECT 'DEF','O004'

SELECT AccountCode COLLATE Latin1_General_CS_AS, COUNT(OrderId) FROM #Temp GROUP BY accountCode COLLATE Latin1_General_CS_AS

ROLLBACK TRAN

Upvotes: -1

Chanukya
Chanukya

Reputation: 5893

create table #a
(
AccountCode varchar(10)  , OrderId varchar(10)
)
insert into #a values
('ABC','O001'),
('ABC','O002'),
('abc','O003'),
('DEF','O004')

select accountCode COLLATE Latin1_General_CS_AS, count(OrderId) from #a group by accountCode COLLATE Latin1_General_CS_AS

Upvotes: 2

Related Questions