Search code examples
sqloracle-databaseoracle12c

Distinct Conditional Counting to Avoid Overlap

Consider this table:

[Table1]
------------------------
| Person_ID | Yes | No |
|-----------|-----|----|
|     1     |  1  | 0  |
|-----------|-----|----|
|     1     |  1  | 0  |
|-----------|-----|----|
|     2     |  0  | 1  |
|-----------|-----|----|
|     2     |  0  | 1  |
|-----------|-----|----|
|     3     |  1  | 0  |
|-----------|-----|----|
|     3     |  1  | 0  |
|-----------|-----|----|
|     3     |  0  | 1  |
|-----------|-----|----|
|     3     |  1  | 0  |
------------------------

I need a distinct count on Person_ID to get the number of people that are marked Yes and No. However, if someone has a single instance of No, they should be counted as a No and not be included in the Yes count no matter how many Yes they have.

My first thought was to try something similar to:

select count(distinct (case when Yes = 1 then Person_ID else null end)) Yes_People
     , count(distinct (case when No = 1 then Person_ID else null end)) No_People
from Table1

but this will result in 3 being counted in both the Yes and No counts.

My desired output would be:

--------------------------
| Yes_People | No_People |
|------------|-----------|
|      1     |     2     |
--------------------------

I'm hoping to avoid the performance hit from having to evaluate a subquery against each row but if it has to be the way to go I will accept that.

Solution

  • You can first group them by the person.
    Then the CASE for the Yes people can have a not No condition.

    SELECT 
 COUNT(CASE WHEN No = 0 AND Yes = 1 THEN Person_ID END) AS Yes_People,
 COUNT(CASE WHEN No = 1 THEN Person_ID END) AS No_People
FROM 
(
     select Person_ID
     , MAX(Yes) as Yes
     , MAX(No) as No
     FROM Table1
     GROUP BY Person_ID
) q