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

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#
gotqn Reply to 2017-11-13 10:53:55Z

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.

Leran2002
3#
Leran2002 Reply to 2017-11-13 10:48:00Z

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.

Valerica
4#
Valerica Reply to 2017-11-13 11:05:32Z

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

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.335548 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO