SELECT < whatever > FROM ( < your first select here > ) dt WHERE ... GROUP by dt.Region, ...
>SELECT DISTINCT (case when season = 'fall' then COUNT(bfblue_data.FAIR_ID) else 0 end) as falltotal , >(case when season = 'spring' then COUNT(bfblue_data.FAIR_ID) else 0 end) as springtotal , >(case when jobdesc = 'SBF4154 (Blue, V2)' then COUNT(bfblue_data.FAIR_ID) else 0 end) as v2total , >(case when jobdesc = 'SBF4154 (Red, V3)' then COUNT(bfblue_data.FAIR_ID) else 0 end) as v3total , >(case when jobdesc = 'SBF4154 (Red, V1)' then COUNT(bfblue_data.FAIR_ID) else 0 end) as v1total , master.Region, bfblue_data.jobDesc, master.season, 'all' AS type >FROM bfblue_data INNER JOIN > master ON bfblue_data.FAIR_ID = master.FairNumber >GROUP BY master.Region, bfblue_data.jobDesc, master.season >UNION >SELECT DISTINCT > (case when season = 'fall' then COUNT(bfblue_data.FAIR_ID) else 0 end) as falltotal , >(case when season = 'spring' then COUNT(bfblue_data.FAIR_ID) else 0 end) as springtotal , >(case when jobdesc = 'SBF4154 (Blue, V2)' then COUNT(bfblue_data.FAIR_ID) else 0 end) as v2total , >(case when jobdesc = 'SBF4154 (Red, V3)' then COUNT(bfblue_data.FAIR_ID) else 0 end) as v3total , >(case when jobdesc = 'SBF4154 (Red, V1)' then COUNT(bfblue_data.FAIR_ID) else 0 end) as v1total , > master.Region, bfblue_data.jobDesc, master.season, 'week' AS type >FROM bfblue_data INNER JOIN > master ON bfblue_data.FAIR_ID = master.FairNumber AND bfblue_data.updateddate > dateadd(day,-30,getdate()) >GROUP BY master.Region, bfblue_data.jobDesc, master.season >ORDER BY type, master.season,master.Region, bfblue_data.jobDesc > >Results >falltotal springtotal v2total v3total v1total Region jobDesc season type >----------- ----------- ----------- ----------- ----------- ----------- -------------------- ---------- ---- >582 0 582 0 0 1 SBF4154 (Blue, V2) fall all >181 0 0 0 181 1 SBF4154 (Red, V1) fall all >34 0 0 34 0 1 SBF4154 (Red, V3) fall all>