Add a distinct count column in table-CodePudding

The scenario below is what I'm trying to accomplish. I've tried using subqueries unsuccessfully.

Basically, I want a distinct count of locations by date. When I try it assigns a 1 for every location, but I only want to assign a 1 to a single instance of the location by date and 0's for all the others.

Date    Name    Location    Distinct Count of locations by date 
1-Feb   Lands   Orlando                     1
1-Feb   Magel   Orlando                     0
1-Feb   Posel   Orlando                     0
1-Feb   Orm     Detroit                     1
1-Feb   Keel    Los Angeles                 1
---------------------------------------------
2-Feb   Lands   Orlando                     1
2-Feb   Magel   Orlando                     0
2-Feb   Posel   Orlando                     0
2-Feb   Orm     Detroit                     1
2-Feb   Keel    Los Angeles                 1

CodePudding user response：

We can use ROW_NUMBER here:

SELECT Date, Name, Location,
       CASE WHEN ROW_NUMBER() OVER (PARTITION BY Date, Location ORDER BY Name) = 1
            THEN 1 ELSE 0 END AS [Distinct Count of locations by date]
FROM yourTable
ORDER BY Date, Location, Name;