How do I split an array between GROUP BY groups?

Question

I'm trying to create a query which will put some rows in multiple groups. I'm doing this by generating an array that contains both categories I want to group by.

Here's my query so far (slightly simplified to remove extraneous details but substantially similar to avoid the risk of erroneous changes):

SELECT 
    case when s.compliant then true else false end as "Compliant",
    (consequences ->> 'Face Covering or Respirator'='Required' ) as "Masked",
    case 
      when (consequences ->> 'Face Covering or Respirator'='Required') and s.compliant
      then '{"Masked","Compliant"}'
      
      when (consequences->>'Face Covering or Respirator'='Required') then '{"Masked"}'
      when s.compliant then '{"Compliant"}'
      else '{"Non-compliant"}'
      end::text[] as "Category",
    COUNT(*) AS "Total"

FROM employees
LEFT JOIN surveys s ON s.uid = employees.uid
GROUP BY s.compliant, "Category", "Masked"

Basically, I want 3 totals in the Totals column, even though these groups are not mutually exclusive:

1. those who are compliant (whether they must wear a mask or not!)
2. those who must wear a mask and are compliant (all of these people are also in the total for # 1)
3. those who are non-compliant

Right now my results are

| Compliant     | Masked     | Category                          |     Total |
|----------------------------------------------------------------------------|
| true          |            | {Compliant}                       |         2 |
| true          | true       | {Masked,Compliant}                |        10 |
| false         |            | {Non-compliant}                   |    63,204 |

But I want to group by Array ITEMS instead of ARRAYS, like so:

| Compliant     | Masked     | Category                          |     Total |
|----------------------------------------------------------------------------|
| true          |            | Compliant                         |        12 |
| true          | true       | Masked                            |        10 |
| false         |            | Non-compliant                     |    63,204 |

(If the data doesn't seem to make sense: a person can be compliant with the policy either by getting jabbed or by agreeing to wear a mask, the only options on the survey. Anyone who has not yet taken the survey is considered non-compliant.)

Note the different total for compliant. How can I get the second result set instead of the first?

Answer 1

I think, this is what you really want:

SELECT count(*) FILTER (WHERE s.compliant) AS "Compliant"
     , count(*) FILTER (WHERE s.compliant
                        AND (s.consequences ->> 'Face Covering or Respirator' = 'Required')) AS "Masked"
     , count(*) FILTER (WHERE s.compliant IS NOT true) AS "Non-compliant"
FROM   employees e
LEFT   JOIN surveys s USING (uid)

Your original, pivoted approach would be much harder.
Assuming e.consequences. Might be optimized further, but there's not enough information.

About the aggregate FILTER:

• Return counts for multiple ranges in a single SELECT statement

Your original query seems subtly broken:

SELECT 
    case when s.compliant then true else false end as "Compliant",
...
GROUP BY s.compliant, ...

"Compliant" <> s.compliant
You display true / false, but form three groups of true / false / null - probably not as intended.