Sql-server – removing group by clause with SUM

sql server

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.

Best Answer

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?

Related Question