CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2012
barracuda
barracuda

Reputation: 1048

Microsoft SQL Query Get count of DISTINCT values using Over Partition

I am trying to get a Count for all the Distinct Case Sensitive values using Over Partition.

I have the following values: 

FirstCol    SecCol

A           E
A           E
A           a
A           e

I want my results to be:

SecCol    FirstCol    SecColCount
a         A           1
e         A           1
E         A           2

I am trying to achieve this using the OVER PARTITION method below, but I am getting the following results with my Query:

SecCol    FirstCol    SecColCount
a         A           1
e         A           3

Here is the Query I am trying to use: 

SELECT Distinct SecCol, FirstCol, 'SecColCount' = count(SecCol) OVER (PARTITION BY SecCol) 
FROM #LocalTempTable;

Upvotes: 2

Views: 106

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

Your default collation for tempdb/db is case insensitive. Use case sensitive instead:

SELECT DISTINCT 
  SecCol COLLATE sql_latin1_general_cp1_cs_as,
  FirstCol,
  [SecColCount] = COUNT(SecCol) OVER (PARTITION BY SecCol 
                                      COLLATE sql_latin1_general_cp1_cs_as) 
FROM #LocalTempTable;

LiveDemo

Upvotes: 3

Related Questions