SQL Group By With Fragmentation

March 15, 2022

I have this data

   UserId   DepartmentId      LoginDate
    100          1            2022-03-01
    101          2            2022-03-01     
    102          2            2022-04-05
    103          3            2022-04-05
    104          3            2022-04-05
    105          1            2022-06-07

and I need this output

LoginDate     TotalPerson    Dep1Person  Dep2Person  Dep3Person  
2022-03-01         2             1            1          0
2022-04-05         3             0            1          2
2022-06-07         1             1            0          0

I know using group by with count. But also I need each department persons count. Could you help me about SQL query.

>Solution :

Schema SQL

CREATE TABLE test (
  UserId INT,
  DepartmentId INT,
  LoginDate DATE
);
INSERT INTO test VALUES (100, 1, '2022-03-01');
INSERT INTO test VALUES (101, 2, '2022-03-01');
INSERT INTO test VALUES (102, 2, '2022-04-05');
INSERT INTO test VALUES (103, 3, '2022-04-05');
INSERT INTO test VALUES (104, 3, '2022-04-05');
INSERT INTO test VALUES (105, 1, '2022-06-07');

Query SQL

SELECT 
LoginDate, 
SUM(1) AS TotalPerson,
SUM(CASE DepartmentId WHEN 1 THEN 1 ELSE 0 END) AS Dep1Person,
SUM(CASE DepartmentId WHEN 2 THEN 1 ELSE 0 END) AS Dep2Person,
SUM(CASE DepartmentId WHEN 3 THEN 1 ELSE 0 END) AS Dep3Person
FROM test
GROUP BY LoginDate

Result