Home Distinct count of users grouped by a column and distributed across bins

# Distinct count of users grouped by a column and distributed across bins

Moller
1#
Moller Published in 2017-11-13 10:30:34Z
 I have several users that each have a department they belong to and a integer which denotes (in days) when they last logged in. A user can have several departments. I am trying to group the users by department, vertically distribute them across three bins depending on when they logged in, and then uniquely count them. If a user belongs to several departments i only want to count them once, it dosen't matter under which department they are being counted. In my current solution a user with several departments gets counted once for each department, which, given my solution, makes sense, but is not what I want. If i try a distinct count it returns the same as each row is unique. I'm using the MsSql Fiddle example except that I have added a DaysSinceLastLogin (int) column that denotes when the user last logged in. Uncomment the first sql block to see the 6 users and which departments they belong to. Fiddle here
gotqn
2#
 Try this:  WITH DataSource AS ( SELECT * ,ROW_NUMBER() OVER (PARTITION BY [User] ORDER BY (SELECT 1)) AS [RowID] FROM yourtable ) select Department, COUNT(case when DaysSinceLastLogin < 10 then 1 end) as 'Last Login within 10 days', COUNT(case when DaysSinceLastLogin > 10 and DaysSinceLastLogin <= 20 then 1 end) as 'Last Login within 10-20 days', COUNT(case when DaysSinceLastLogin > 20 then 1 end) 'Last Login more then 20 days' FROM DataSource WHERE [RowID] = 1 GROUP BY Department;  The idea is to use ROW_NUMBER to set ID for each department for particular user. Then to get data only for the first one of this departments.
 As variant you can use a subquery SELECT MaxDepartment, COUNT(case when MaxDaysSinceLastLogin < 10 then 1 end) as 'Last Login within 10 days', COUNT(case when MaxDaysSinceLastLogin > 10 and MaxDaysSinceLastLogin <= 20 then 1 end) as 'Last Login within 10-20 days', COUNT(case when MaxDaysSinceLastLogin > 20 then 1 end) 'Last Login more then 20 days' FROM ( SELECT [User], -- any department MAX([Department]) MaxDepartment, -- max of days MAX([DaysSinceLastLogin]) MaxDaysSinceLastLogin FROM yourtable GROUP BY [User] ) q GROUP BY MaxDepartment  I hope I understood you correctly.
 If it doesn't matter under which department they will be counted you can change the department inside of an CTE and the use your query: ;WITH CTE AS ( SELECT DISTINCT [User] ,CASE WHEN department = (SELECT top 1 department FROM yourtable y WHERE [User] = t.[User]) THEN department ELSE (SELECT top 1 department FROM yourtable y WHERE [User] = t.[User]) END AS department ,[DaysSinceLastLogin] FROM yourtable t ) select Department, COUNT(case when DaysSinceLastLogin < 10 then 1 end) as 'Last Login within 10 days', COUNT(case when DaysSinceLastLogin > 10 and DaysSinceLastLogin <= 20 then 1 end) as 'Last Login within 10-20 days', COUNT(case when DaysSinceLastLogin > 20 then 1 end) 'Last Login more then 20 days' FROM CTE GROUP BY Department  Also HERE