1

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?

2

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'
  )
;
2
  • can you run max() on strings?
    – tadamhicks
    Aug 14 '16 at 17:07
  • 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
    Aug 14 '16 at 21:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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