Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
What do you mean with the "I have 200 cnt_* fields"? What are these? Calculated columns? –  ypercube May 4 '12 at 21:36

2 Answers 2

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?

share|improve this answer
    
Yes. I beleive a1ex07 has the right idea (though I havn't tried your code and I am not sure what the PARTITION is doing, still learning). The problem with a1ex07's idea is that some of the columns change depending on how its grouping by. If I GROUP BY A, B, C then my id = NULL but if I GROUP BY A, B then id = 100000 for example –  masfenix May 7 '12 at 13:50

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)

share|improve this answer
    
do you have an example or documentation you might be able to share? –  masfenix May 5 '12 at 17:29
    
    
Hi, the problem is that some of the columns, for example school in the original post, change depending on how its grouped by. –  masfenix May 7 '12 at 13:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.