removing group by clause with SUM

Question

So lets say I have a table, MyBigTable that has student scores of an examination. So lets say the structure looks something like

id
name
school 
board
exam_partA_score
exam_partB_score 

Then I need to run some script on it at certain levels. So lets say I have to run report at the school level so I will have

select 
 school, 
 board, 
 cnt_whoPassedA = SUM (CASE WHEN A.exam_PartA_score > 50 THEN 1 ELSE 0 END)
 cnt_whoPassedB = SUM (CASE WHEN A.exam_PartA_score > 50 THEN 1 ELSE 0 END)
from MyBigTable as A
group by A.school, A.board

Then I need to run another script at the board level so

select 
 school = null, 
 board, 
 cnt_whoPassedA = SUM (CASE WHEN A.exam_PartA_score > 50 THEN 1 ELSE 0 END)
 cnt_whoPassedB = SUM (CASE WHEN A.exam_PartA_score > 50 THEN 1 ELSE 0 END)
from MyBigTable as A
group by A.board

Then at the end I need to combine them all so something like

select * into BigReport 
from First_Select Statement UNION Second_select_statement

Problem: I have about 6 fields in the group by clause. They change depending on the type of report I need (school level, board level, state level).

I also have about 200 cnt_* fields.

Right now I have those "select" statements three times at the different levels which makes my script around 900 lines.

I dont see why I have to compute counts three times when the logic they use is the same all three times. I just need to change the headers (school, board, id..) and the group by clause. Not only that, if a spec changes for a count, or another count field is needed then I am going to have to change it in three different places which is very error prone.

What is the best and most efficient way of doing this.

Answer 1

Take your most complex (most GROUP BY columns) then use the OVER clause

SELECT
   school, board, col3, col4, ...,
   cnt_whoPassedA, ...,
   SUM(cnt_whoPassedA) OVER (PARTITION BY board) AS cnt_whoPassedA_ByBoard,
   SUM(cnt_whoPassedA) OVER (PARTITION BY school) AS cnt_whoPassedA_BySchool,
   SUM(cnt_whoPassedA) OVER (PARTITION BY school, board) AS cnt_whoPassedA_BySchoolBoard,
   ...
FROM
(
select 
 school, 
 board, 
 col3, col4, ...
 cnt_whoPassedA = SUM (CASE WHEN A.exam_PartA_score > 50 THEN 1 ELSE 0 END)
 cnt_whoPassedB = SUM (CASE WHEN A.exam_PartA_score > 50 THEN 1 ELSE 0 END)
from MyBigTable as A
group by A.school, A.board, A.col3, A.col4...
) T

However, I would consider doing this in my report layer (say in SSRS): do you need all levels of grouping at the same time?

Answer 2

I believe GROUP BY ROLLUP (A.board,A.school) (SQLServer 2005 and up) will work for you. For more complex grouping you may want to use GROUPING SETS (2008 and up)