T-SQL is Union All's Best Subgroup Analysis

Question

I have a database with student information and school classes. Then I try to query a result set showing key performance indicators for the different student subgroups we have at school. A very crude example of the end result:

Example of the table I'm trying to create
Please note that student groups overlap.
One and the same pupil may be included in Whole Year, Femalesand Non FSMetc .:

Now I have achieved this. I used aggregated functions and a GROUP BY clause for each subgroup, and then combined these queries with UNION ALL to create a table. A striking example of my code would be:

SELECT 'Whole Year' AS [Group], COUNT(student.name) AS [Total No. of Students]

UNION ALL

SELECT student.gender AS [Group], COUNT(student.name) AS [Total No. of Students]
GROUP BY student.gender

UNION ALL

SELECT student.fsm AS [Group], COUNT(student.name) AS [Total No. of Students]
GROUP BY student.fsm

, . HUGE, .

, , , , , - , .

Answer 1

-, . ( student). UNPIVOT, , , .

CTE ( ), student KPI :

;
with group_cte (student_group_name, student_id)
AS
(
    select student_group, student_id
    from 
    (select e.student_id as total, 
        case when gender = 'male' then e.student_id end as males, 
        case when gender = 'females' then e.student_id end as females,
        -- your other groupings
    from student e) p
    UNPIVOT
    (student_id for student_group 
        in (total, males, females)) unpvt
)
select student_group_name, count(student_id), -- your KPIs...
from group_cte c
inner join student s
on e.student_id = c.student_id
group by student_group_name

SQL.