Search code examples
countteradatadistinctmultiple-conditions

How to count number of distinct ID's where status does not match

I have a dataset that looks similar to the below:

Sample Table

I would like to count the total number of distinct ID's that have both a 'SEND' and 'REC'. In other words, where the status does not match (values are limited to SEND and REC for Status field). In this case, the desired query would return a value of 2 since there are 2 distinct ID's that have both a 'SEND' and 'REC' in the dataset.

I tried the following query but did not work since there could only be one status per row and this query is looking for both of those status' within one row.

SELECT COUNT(DISTINCT ID) FROM Table WHERE Date BETWEEN '2022-01-19' AND '2022-01-19' AND Status = 'SEND' AND Status = 'REC' ;

Solution

  • If it's only two distinct values the most efficient way is probably:

    select count(*)
from
 (
   SELECT ID 
   FROM Table 
   WHERE Date BETWEEN '2022-01-19' AND '2022-01-19' 
     AND Status IN ('SEND','REC')    -- only two possible values per id
   GROUP BY ID
   HAVING MIN(Status) <> Max(Status) -- both values exist
 ) as dt ;