How to sub total column value's in pivot table (MySQL) with group by clause

Question

I have a pivot table with the respective fields.

1. district
2. block
3. MaleCount
4. FemaleCount

above are a column name. So, basically, I have grouped all those fileds by district and block-wise. For that, I have written the following query in MySQL.

select 
ds.ds_name as district,
bs.bl_name as block,

case
when sd.gender_id = 1 then count(gender_id)
else 0 
end as MaleCount,

case
when sd.gender_id = then count(gender_id)
else 0
end as FemalCount,

from studet data sd
inner join district ds on ds.district_id = sd.ds_id

inner join block bs on bs.block_id = sd.bs_id

group by bs.block_name, ds.district_name;

which gives the following result

    district | block | FemalCount | MaleCount

    xyz      | abc   | 4          | 5

             | cvz   | 5          | 9

    ytz      | tyz   | 7          | 3

             | awe   |3           |8

The main thing is, I want the following kind of the result set.

   district | block | FemalCount | MaleCount

    xyz      | abc   | 4          | 5

             | cvz   | 5          | 9

    total    |       | 9          | 14

    ytz      | tyz   | 7          | 3

             | awe   | 3          | 8

    total    |       | 10         | 11

which includes a subtotal row for Malecount and FemaleCount with a group by block and district as mention above. How could I achieve this in MySQL query?

Answer 1

You could add a union all (with high values for the block name to push it to the end on the order by) to calculate the totals by district so given

+----------+-----------+-------+-------+
| sudentid | gender_id | ds_id | bs_id |
+----------+-----------+-------+-------+
|      101 |         2 |     3 |     2 |
|      103 |         2 |     3 |     2 |
|      112 |         1 |     3 |     3 |
|      116 |         1 |     3 |     3 |
|      117 |         1 |     3 |     3 |
|        1 |         1 |     1 |     1 |
|        2 |         1 |     1 |     1 |
|        3 |         1 |     1 |     1 |
+----------+-----------+-------+-------+
8 rows in set (0.00 sec)

DROP TABLE IF EXISTS DISTRICT,BLOCK;

CREATE TABLE DISTRICT (ID INT, name varchar(10));
create table block(id int, name varchar(10));

insert into district values (1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd');

insert into block values (1,'E'),(2,'F'),(3,'G');

select 
         case when block = 'zzz' then 'Total'
                else    district
         end as district,
         case when block = 'zzz' then ''
         else block
         end as block, 
         malecount,femalecount
from
(
select 
ds.name as district,
bs.name as block,
sum(case when gender_id = 1 then 1 else 0 end) Malecount,
sum(case when gender_id = 2 then 1 else 0 end) Femalecount
from student sd
inner join district ds on ds.id = sd.ds_id
inner join block bs on bs.id = sd.bs_id
group by ds.name, bs.name

union all
select district,'zzz', malecount,femalecount
from
(
select ds.name as district,
sum(case when gender_id = 1 then 1 else 0 end) Malecount,
sum(case when gender_id = 2 then 1 else 0 end) Femalecount
from student sd
inner join district ds on ds.id = sd.ds_id
group by ds.name
) s
) t
order by t.district,t.block
;

Result

+----------+-------+-----------+-------------+
| district | block | malecount | femalecount |
+----------+-------+-----------+-------------+
| aaa      | E     |         3 |           0 |
| Total    |       |         3 |           0 |
| ccc      | F     |         0 |           2 |
| ccc      | G     |         3 |           0 |
| Total    |       |         3 |           2 |
+----------+-------+-----------+-------------+
5 rows in set (0.00 sec)

Note the slightly less verbose conditional aggregation (sum(case when...)