Select count distinct on key column where multiple others satisfy condition

Question

I have a table where I am trying to find a count distinct on a column that is not a the primary key

account_id | media_id | sat_cond1 | sat_cond2 --------------------------------------------- 123 | 333 | Y | N 123 | 334 | N | Y 123 | 335 | N | N 124 | 221 | N | Y 124 | 222 | N | N 125 | 111 | Y | Y

I'm trying to figure out a query that will return a count of account_ids where there's at least one row that satisfies sat_cond1 and at least one row that satisfies sat_cond2.

For instance, in the above table account_id 123 would, because media_id 333 satisfies sat_cond1 and 334 satisfies sat_cond2. account_id 124 would not be counted, however, because though media_id 221 satisfies sat_cond2 there is no media_id that satisfies sat_cond1 for it. account_id 125 meets all conditions on a single media_id, so it would be counted.

What sort of query would give me this?

Balazs Papp · Accepted Answer · 2016-08-14 17:01:05Z

Create groups with GROUP BY based on account_id. Find the MAX of sat_cond1 and sat_cond2 for each group. If the MAX values equal Y for both condition, keep the account_id. Count the distinct account_id values.

Above translated to SQL:

select count(*) from ( select account_id from data group by account_id having max(sat_cond1) = 'Y' and max(sat_cond2) = 'Y' ) ;

Or, perhaps, without a derived table e.g. like this: select count(count(*)) from data group by account_id having max(sat_cond1) = 'Y' and max(sat_cond2) = 'Y'. Although that would probably be less clear. — Andriy M
– Andriy M, Commented Aug 14, 2016 at 21:19

Stack Exchange Network

Select count distinct on key column where multiple others satisfy condition

1 Answer 1

Hot Network Questions

Select count distinct on key column where multiple others satisfy condition

1 Answer 1

Related

Hot Network Questions