How to pivot with row total and column total?

Transact-SQL
1

Hi, my SQL guru,

Is there a way to achieve what I want in the example below? I knew someone has said this is report writer's job but not t-sql's strenght. Still I like to find out.

CREATE TABLE #StuCounts
(Gender VARCHAR(10),
Grade VARCHAR(5), Counts int);
GO

INSERT INTO #StuCounts VALUES('Male', '01', 200);
INSERT INTO #StuCounts VALUES('Male', '02', 400);
INSERT INTO #StuCounts VALUES('Male', '03', 450);
INSERT INTO #StuCounts VALUES('Male', '04', 430);
INSERT INTO #StuCounts VALUES('Male', '05', 410);
INSERT INTO #StuCounts VALUES('Female', '01', 150);
INSERT INTO #StuCounts VALUES('Female', '02', 405);
INSERT INTO #StuCounts VALUES('Female', '03', 485);
INSERT INTO #StuCounts VALUES('Female', '04', 285);
INSERT INTO #StuCounts VALUES('Female', '05', 101);

select * from #StuCounts
pivot (SUM(counts)
for Gender in ([Male],[Female])
) as PivotCount

drop table #StuCounts

My Select here will yield this:

Grade Male Female
01 200 150
02 400 405
03 450 485
04 430 285
05 410 101

But they expect to have something extra, as in far right column and last row.
Grade Male Female total_by_grade
1 200 150 350
2 400 405 805
3 450 485 935
4 430 285 715
5 410 101 511
Total 1890 1426 3316

thanks!

2 
SELECT Grade,
    SUM(CASE WHEN Gender = 'Male' THEN 1 ELSE 0 END) AS Male,
    SUM(CASE WHEN Gender = 'Female' THEN 1 ELSE 0 END) AS Female,
    SUM(1) AS Total_By_Grade
FROM #StuCounts
GROUP BY Grade
1 Like
3

Scott,

Yours got me this:
Grade Male Female Total_By_Grade
01 1 1 2
02 1 1 2
03 1 1 2
04 1 1 2
05 1 1 2

And my modified got this:
SELECT Grade,
SUM(CASE WHEN Gender = 'Male' THEN counts ELSE 0 END) AS Male,
SUM(CASE WHEN Gender = 'Female' THEN counts ELSE 0 END) AS Female,
SUM( 1) AS Total_By_Grade
FROM #StuCounts
GROUP BY Grade

Grade Male Female Total_By_Grade
01 200 150 2
02 400 405 2
03 450 485 2
04 430 285 2
05 410 101 2

But neither game me what I want.

What the third SUM() suppose to work?

4

SELECT Grade,
SUM(CASE WHEN Gender = 'Male' THEN counts ELSE 0 END) AS Male,
SUM(CASE WHEN Gender = 'Female' THEN counts ELSE 0 END) AS Female,
SUM(counts) AS Total_By_Grade
FROM #StuCounts
GROUP BY Grade
ORDER BY Grade

1 Like
5

Ok, great!
Now I only have one issue left. That is how to get the last row as in my sample?
Thanks!

6

Correcting...

1 Like
7

Try this, we'll see if it works:

SELECT CASE WHEN Grade IS NULL THEN 'Total' ELSE Grade END AS Grade,
SUM(CASE WHEN Gender = 'Male' THEN counts ELSE 0 END) AS Male,
SUM(CASE WHEN Gender = 'Female' THEN counts ELSE 0 END) AS Female,
SUM(counts) AS Total_By_Grade
FROM #StuCounts
GROUP BY Grade WITH ROLLUP
ORDER BY Grade

1 Like
8

Perfect!

Now my only regret is that my sample is too simple. I need to apply your solution to the real data, fingers crossed.

Thanks!

9

My table is slightly different than what was provided here. I don't have the counts field. So using the same #StuCounts table and ignoring the counts field I came up with this query but this only gives me the total male and female. How do I tweak this so that it also gives me the total by grade.

SELECT CASE WHEN Grade IS NULL THEN 'Total' ELSE Grade END AS Grade,
SUM(CASE WHEN Gender = 'Male' THEN 1 ELSE 0 END) AS Male,
SUM(CASE WHEN Gender = 'Female' THEN 1 ELSE 0 END) AS Female
--SUM(Male) AS Total_By_Grade
FROM #StuCounts
GROUP BY Grade WITH ROLLUP
ORDER BY Grade