Aggregation by Subgroup 1 or 0

Question

I am trying to aggregate Data by creating subgroup from a table for an Indicator which is 1 or 0. I could figure our how to create a subgroup for indicator met=1 and indicator met =0 separately and i can UNION them for my required Output. Any query idea on how i could write a single query to achieve this? instead of UNIONING two queried results.

Answer 1

Try grouping by your case statement:

Select 'Chicago' as region
  , district
  , school
  , case indicator when 0 then 'NOT MET' when 1 then 'MET' end as Indicator
  , count(distinct ID) as total
[etc.]
from Table
where year = 2013
and indicator in (1,0) --optional, if these are the only possible values
group by 
    district
    , school
    , case indicator when 0 then 'NOT MET' when 1 then 'MET' end 

May also want to consider adding with rollup to the group by

Answer 2

How about adding the indicator to the grouping sets?

SELECT 'Chicago' AS Region, District, SchoolName AS School,
       (CASE WHEN indicator = 1 THEN 'Indicator Met' ELSE 'Indicator Not Met'
        END) AS Type,
       COUNT(DISTINCT id) AS Total, SUM(Flag) AS Met
FROM final.table
WHERE Year = 2013
GROUP BY GROUPING SETS(( district, indicator ),
                       ( district, Schoolname, indicator ), (indicator));

Answer 3

Perhaps a sub query with your indicator grouping.

SELECT
 *
FROM
(
    SELECT  'Chicago' AS Region ,
               District ,
                SchoolName AS School,
                'Indicator Met' AS Type,
            COUNT(DISTINCT id) AS Total ,
            SUM(Flag) AS Met,
            Indicator
    FROM    final.table
    WHERE   Year = 2013
    GROUP BY Indicator,District,SchoolName  
)AS X
WHERE
    Indicator IN(0,1)
GROUP BY GROUPING SETS(( district ),
                       ( district, Schoolname ), ( ))