SQL Group By With Fragmentation-CodePudding

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.

CodePudding user response：

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

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

https://www.db-fiddle.com/f/bXkzNxRt21bevEWg9gHH5n/0

CodePudding user response：

This should work.

SELECT *
    , COUNT(CASE WHEN DepartmentID = 1 THEN 1 END) AS Dep1Person
    , COUNT(CASE WHEN DepartmentID = 2 THEN 1 END) AS Dep2Person
    , COUNT(CASE WHEN DepartmentID = 3 THEN 1 END) AS Dep3Person
FROM `test`
WHERE 1
GROUP BY LoginDate

Total persons left as homework. :-)