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