sqlpostgresqlgroup-by

Aggregate status based on presence of at least one match


Here's my Postgres table schema: db<>fiddle

create table my_table
 (id, name,     status, realm_id)as values
 (1,  'cash',   'denied',    123)
,(2,  'check',  'closed',    123)
,(3,  'payroll','denied',    123)
,(4,  'cash',   'pending',   456)
,(5,  'deposit','suspended', 456)
,(6,  'lending','expired',   456)
,(7,  'loan',   'trial',     456)
,(8,  'crypto', 'active',    456)
,(9,  'payroll','closed',    456);

The result that I'd like to get is something like this:

realm_id status
123 inactive
456 active

So two dimensions of aggregation:

  1. aggregate based on realm_id first;
  2. aggregate based on status: as long as the realm_id has a name which status is neither closed nor denied, it'll be marked as active, otherwise, it's inactive.

I've tried to use aggregate and left outer join, but no luck thus far.

Any ideas would be greatly appreciated!


Solution

  • You can use EVERY combined with a GROUP BY clause to check if every row per realm_id has status closed or denied. Using a CASE expression, you can then set the status to active or inactive.

    SELECT
      realm_id,
      CASE 
        WHEN EVERY(status in ('closed', 'denied')) 
          THEN 'inactive'
          ELSE 'active' END AS status
    FROM yourtable
    GROUP BY realm_id
    ORDER BY realm_id;
    

    I would even prefer to skip the CASE expression and simply return t or f in a column named inactive, that's a matter of taste:

    SELECT
      realm_id,
      EVERY(status in ('closed', 'denied')) AS inactive
    FROM yourtable
    GROUP BY realm_id
    ORDER BY realm_id;
    

    See this db<>fiddle with your sample data.