# How do I get totals in a pivot table in SQL?

March 17, 2006 3:40 PM Subscribe

I have an SQL query that totals the number of students per grade. How do I get a grand total of the number of students?

For example, let's say I have a table with two columns: grade as VARCHAR(2) and schoolname as VARCHAR(20). I used the following nifty query in Anthony Molinaro's SQL Cookbook:

I use a UNION to add other schools so my output looks like this:

I want totals. How do I get this:

I'm using SQL Server 2003, BTW.

p.s. Is this a pivot table?

For example, let's say I have a table with two columns: grade as VARCHAR(2) and schoolname as VARCHAR(20). I used the following nifty query in Anthony Molinaro's SQL Cookbook:

select schoolname,

sum(case when grade='00' then 1 else 0 end) as [K],

sum(case when grade='01' then 1 else 0 end) as [1],

sum(case when grade='02' then 1 else 0 end) as [2],

sum(case when grade='03' then 1 else 0 end) as [3],

sum(case when grade='04' then 1 else 0 end) as [4],

sum(case when grade='05' then 1 else 0 end) as [5],

sum(case when grade='06' then 1 else 0 end) as [6]

from tblRoosevelt

group by name

I use a UNION to add other schools so my output looks like this:

schoolname K 1 2 3 4 5 6

------------------------------------------------

Roosevelt 82 99 90 89 92 86 93

Lincoln 77 82 88 92 92 94 91

Washington 39 79 80 73 86 85 77

I want totals. How do I get this:

schoolname K 1 2 3 4 5 6 TOTAL

--------------------------------------------------------

Roosevelt 82 99 90 89 92 86 93 631

Lincoln 77 82 88 92 92 94 91 616

Washington 39 79 80 73 86 85 77 1247

I'm using SQL Server 2003, BTW.

p.s. Is this a pivot table?

select schoolname,

sum(case when grade='00' then 1 else 0 end) as [K],

sum(case when grade='01' then 1 else 0 end) as [1],

sum(case when grade='02' then 1 else 0 end) as [2],

sum(case when grade='03' then 1 else 0 end) as [3],

sum(case when grade='04' then 1 else 0 end) as [4],

sum(case when grade='05' then 1 else 0 end) as [5],

sum(case when grade='06' then 1 else 0 end) as [6],

sum(1) as TOTAL

from tblRoosevelt

group by name

posted by mbrubeck at 3:48 PM on March 17, 2006

sum(case when grade='00' then 1 else 0 end) as [K],

sum(case when grade='01' then 1 else 0 end) as [1],

sum(case when grade='02' then 1 else 0 end) as [2],

sum(case when grade='03' then 1 else 0 end) as [3],

sum(case when grade='04' then 1 else 0 end) as [4],

sum(case when grade='05' then 1 else 0 end) as [5],

sum(case when grade='06' then 1 else 0 end) as [6],

sum(1) as TOTAL

from tblRoosevelt

group by name

posted by mbrubeck at 3:48 PM on March 17, 2006

...or you can replace 'sum(1)" in my answer with "count(*)", which is more idiomatic but less similar to the other parts of your query. (This is just a stylistic choice, really. Either way should give you the same answer.)

posted by mbrubeck at 3:52 PM on March 17, 2006

posted by mbrubeck at 3:52 PM on March 17, 2006

Best answer: Another note (sorry):

My solution counts every row in the table, not just ones where the grade is between 00 and 06. If there might be other grades, and you wanted to total only those grades, then you should do:

sum(case when grade in ('00', '01', '02', '03', '04', '05', '06') then 1 else 0 end) as TOTAL

posted by mbrubeck at 3:55 PM on March 17, 2006

My solution counts every row in the table, not just ones where the grade is between 00 and 06. If there might be other grades, and you wanted to total only those grades, then you should do:

sum(case when grade in ('00', '01', '02', '03', '04', '05', '06') then 1 else 0 end) as TOTAL

posted by mbrubeck at 3:55 PM on March 17, 2006

Response by poster: Yeah, I tried count(*) and realized the same thing. The 'case when grade in' works wonderfully, though! Thank you!

posted by DakotaPaul at 4:11 PM on March 17, 2006

posted by DakotaPaul at 4:11 PM on March 17, 2006

This thread is closed to new comments.

posted by DakotaPaul at 3:41 PM on March 17, 2006