Select rows for which at least one row per set meets a condition

9 votes

4 answers

62485 views

                          I have following table:

    create table test (
      company_id integer not null, 
      client_id integer not null, 
      client_status text,
      unique (company_id, client_id)
    );
    
    insert into test values
      (1, 1, 'y'),    -- company1

      (2, 2, null),   -- company2
    
      (3, 3, 'n'),    -- company3

      (4, 4, 'y'),    -- company4
      (4, 5, 'n'),

      (5, 6, null),   -- company5
      (5, 7, 'n')
    ;

Basically, there are 5 different companies, each one has one or more clients and each client has status: 'y' or 'n' (might be null as well).

What I have to do is to select all pairs (company_id, client_id) for all companies for which there is at least one client whose status is not 'n' ('y' or null). So for the example data above, the output should be:

    company_id;client_id
    1;1
    2;2
    4;4
    4;5
    5;6
    5;7

I tried something with window functions but I can't figure out how to compare the number of ALL clients with the number of clients with STATUS = 'n'.

    select company_id,
    count(*) over (partition by company_id) as all_clients_count
    from test
    -- where all_clients_count != ... ?

I figured out how to do this, but I am not sure if it's the right way:

    select sub.company_id, unnest(sub.client_ids)
    from (
      select company_id, array_agg(client_id) as client_ids
      from test
      group by company_id
      having count(*) != count( (case when client_status = 'n' then 1 else null end) )
    ) sub
                        

Asked by user606521 (1415 rep)

May 31, 2016, 09:36 AM
Last activity: Jul 7, 2025, 10:56 PM

Select rows for which at least one row per set meets a condition

Related Questions