Totals and subtotals--simply

from the Artful Common Queries page


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 
('2013-10-1',1,5), ('2013-10-1',2,6), ('2013-10-1',3,2), ('2013-10-1',3,5), 
('2013-10-2',1,1), ('2013-10-2',1,2), ('2013-10-2',2,3), ('2013-10-2',2,4), 
('2013-10-3',1,2), ('2013-10-3',1,2), ('2013-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.

Return to the Artful Common Queries page