# 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