group by grouping sets and case when grouping

Database
Enthusiast

group by grouping sets and case when grouping

Hello All,

When using group by grouping sets, I have always resorted to using longwinded case statements (as in the example below), but thought there must be an easier and cleaner way...is there?  I know you can use case when grouping (t_team_nm) = 1 then 'Subtotal'...but I have only gotten that to work for subtotals on one field...not subtotals on a combination of multiple fields.

Thanks!

sel

case

when yr is not null and t_team_nm is not null and t_rep_nm is not null then 'All'

when yr is not null and t_team_nm is null and t_rep_nm is not null then 'Rep'

when yr is not null and t_team_nm is not null and t_rep_nm is null then 'Team'

else 'Other'

end as SubTotal,

extract(year from t_activity_dt) as YR,

t_team_nm,

t_rep_nm,

sum(t_gross_adds) as GROSS_ADDS

from

mytbl

group by grouping sets

(

(yr, t_team_nm, t_rep_nm),

(yr, t_team_nm),

(yr, t_rep_nm)

)

1 REPLY
Enthusiast

Re: group by grouping sets and case when grouping

Am I correct in assuming there is no other way to do this without a case statement like the one written above?