Count Dimension Items that All Have the Same Value in Another Field

I have a table that looks like the following: business_id, employee_id, and status, which is either ‘active’ or ‘inactive’

I want to count the number of businesses where ALL of its employees are “active”. What’s the best way to accomplish this?

Answer

Here is one method:

select count(distinct business_id)
from t
where not exists (select 1
                  from t t2
                  where t2.business_id = t.business_id and
                        t2.status <> 'active'
                 );

Or, two levels of aggregation:

select count(*)
from (select business_id
      from t
      group by business_id
      having min(status) = max(status) and min(status) = 'active'
     ) b;