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

March 17, 2006 3:40 PM

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:

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?

posted by DakotaPaul to Computers & Internet (5 answers total)

Response by poster: Oops, here's the SQL Cookbook link.
posted by DakotaPaul at 3:41 PM on March 17, 2006

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

...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

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

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