How to calculate the sum of a column by individual category separately?

I need some help

I need to calculate the total hours of each employee and also show all the dates of each employee in a same table.

My Table is like below-

Employee Date hours
Rohit 06-03-2022 9
Rohit 07-03-2022 8
Rohit 08-03-2022 9
Rohit 09-03-2022 9
Rohit 10-03-2022 10
Rohit 11-03-2022 8
Rohit 12-03-2022 8
Rohit 06-03-2022 7
Raj 07-03-2022 8
Raj 08-03-2022 4
Raj 09-03-2022 3
Raj 10-03-2022 5
Raj 11-03-2022 8
Raj 12-03-2022 8

The output should be like this

Employee Date hours
Rohit 06-03-2022 9
Rohit 07-03-2022 8
Rohit 08-03-2022 9
Rohit 09-03-2022 9
Rohit 10-03-2022 10
Rohit 11-03-2022 8
Rohit 12-03-2022 8
Rohit 06-03-2022 7
Total 68
Raj 07-03-2022 8
Raj 08-03-2022 4
Raj 09-03-2022 3
Raj 10-03-2022 5
Raj 11-03-2022 8
Raj 12-03-2022 8
Total 36

>Solution :

You can use GROUPING SETS for this

SELECT
  Employee = CASE WHEN GROUPING(t.Date) = 0 THEN t.Employee ELSE 'Total' END,
  t.Date,
  hours = SUM(t.hours)
FROM YourTable t
GROUP BY GROUPING SETS (
    (Employee, Date),
    (Employee)
);

db<>fiddle

The GROUPING() function tells you whether a column has been aggregated for that row

Leave a Reply