Aggregate status based on presence of at least one match

Question

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!

Answer 1

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.