Include total column and total row on query using pivot

1

I have the following sql query that uses PIVOT. I want to be able to add the total of the M, F, and U students per grade and the total of all male and female students. Help please. Thanks

SELECT *
FROM (
SELECT
grade
, gender
FROM
STUDENTS
WHERE status = 0
AND schoolid=122)
PIVOT (
count(gender)
for (gender)
in ('M', 'F','U')
)
ORDER BY grade

The output I get now is

grade M F U
10 288 326 0
11 336 285 0
12 300 259 0

But my users want this output to be

grade M F U Total
10 288 326 0 614
11 336 285 0 621
12 300 259 0 559
Tot 924 870 0 1794

2

Here's one way:

DECLARE @students TABLE
(
    status BIT,
    schoolid INT,
    grade INT,
    gender CHAR
);
INSERT INTO @students
(
    status,
    schoolid,
    grade,
    gender
)
VALUES
(0, 122, 1, 'M'),
(0, 122, 2, 'F'),
(0, 122, 3, 'U');

WITH q
AS (SELECT grade,
           gender
    FROM @students
    WHERE status = 0
          AND schoolid = 122),
     p
AS (SELECT CAST(grade AS VARCHAR(30)) AS grade,
           M,
           F,
           U
    FROM q
        PIVOT
        (
            COUNT(gender)
            FOR gender IN ([M], [F], [U])
        ) _)
SELECT IIF(grade IS NULL, 'TOT', grade) AS grade,
       SUM(M) AS M,
       SUM(F) AS F,
       SUM(U) AS U
FROM p
GROUP BY grade WITH ROLLUP;
1 Like