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.
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'
end as SubTotal,
extract(year from t_activity_dt) as YR,
sum(t_gross_adds) as GROSS_ADDS
group by grouping sets
(yr, t_team_nm, t_rep_nm),
Am I correct in assuming there is no other way to do this without a case statement like the one written above?