You have a table that tracks attendance hours ...
drop table if exists t;
create table t(d date, id int, hrs int);
insert into t values
('2024-10-1',1,5), ('2024-10-1',2,6), ('2024-10-1',3,2), ('2024-10-1',3,5),
('2024-10-2',1,1), ('2024-10-2',1,2), ('2024-10-2',2,3), ('2024-10-2',2,4),
('2024-10-3',1,2), ('2024-10-3',1,2), ('2024-10-3',1,2);
... and you need an attendance summary by date and person. It turns out that this aggregating query is easy top write—first write an inner query to Group By date and person With Rollup, then write an outer query that renames the Rollup Null labels.
The inner aggregating query ...
select d,id,sum(hrs) as attended
from t
group by d,id
with rollup;
Now nestle that query inside an outer query whose only reason for existence is to rename the Rollup Nulls to informative labels ...
select
ifnull( d, 'GRAND TOTAL' ) AS Date,
ifnull( id, if( d is null, '', 'DAY TOTAL' )) as ID,
Attended
from (
select d,id,sum(hrs) as Attended
from t
group by d,id
with rollup
) sums;
+-------------+-----------+----------+
| Date | ID | Attended |
+-------------+-----------+----------+
| 2013-10-01 | 1 | 5 |
| 2013-10-01 | 2 | 6 |
| 2013-10-01 | 3 | 7 |
| 2013-10-01 | DAY TOTAL | 18 |
| 2013-10-02 | 1 | 3 |
| 2013-10-02 | 2 | 7 |
| 2013-10-02 | DAY TOTAL | 10 |
| 2013-10-03 | 1 | 6 |
| 2013-10-03 | DAY TOTAL | 6 |
| GRAND TOTAL | | 34 |
+-------------+-----------+----------+
The pattern easily generalises to as many sums as you like. |
|