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):

    case when s.compliant then true else false end as "Compliant",
    (consequences ->> 'Face Covering or Respirator'='Required' ) as "Masked",
      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?

  • Looks like surveys a should be surveys s. Show table definitions (CREATE TABLE statements), your Postgres version and some sample data. Nov 4, 2021 at 21:12
  • Thanks! Fixed ✅. I changed the table name to something more self-explanatory here and missed that change.
    – iconoclast
    Nov 4, 2021 at 21:30
  • 1
    Please provide basic table definitions. "Self-explanatory" is wishful thinking. e.consequences or s.consequences? 1:n or 1:1? Are we counting surveys or unique employees? Nov 4, 2021 at 21:36
  • s.consequences is a JSONB field with consequences of the answers to the survey, in this case potentially the requirement to wear a mask. There should never be more than one survey per employee, but there can easily be zero. The case statement case when s.compliant then true else false end as "Compliant" handles the situation where there is no survey yet, to return false for compliance.
    – iconoclast
    Nov 5, 2021 at 18:35
  • I'll add more info as soon as I'm able...
    – iconoclast
    Nov 5, 2021 at 18:39

1 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:

Your original query seems subtly broken:

    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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.