CODEWITHSUNDEEP
sql
Alex
Alex

Reputation: 71

SQL - Count how many time column occurs with two different values

I have a SQL table that I'm trying to determine the count of for a specific scenario:

Table [References]:
[County], [Vendor], [Status], [ID]

I need to know how many distinct [ID] values exist that contain a [Status] value of "FAILED" that do not also have a [Status] of "COMPLETED". Basically, if there is a failed without a completed, i need the distinct amount for that.

Example:

County_1, Vendor_1, Failed, 12345.12
County_1, Vendor_1, Completed, 12345.12
County_1, Vendor_1, Failed, 12345.32
County_1, Vendor_1, Failed, 12345.32
County_1, Vendor_1, Failed, 12345.52
County_1, Vendor_1, Completed, 12345.52
County_1, Vendor_1, Failed, 12345.72

With this data, it should return a count of 2, since records 3 and 4 are failures that do not have a completed record and record 7 doesn't either. It would be a distinct count so 3 and 4 would count as one since they are the same [ID]

Upvotes: 1

Views: 90

Answers (2)

TheProvost
TheProvost

Reputation: 1893

You can use subquery for this. First extract all the distinct ID with 'failed' status then filter the ID that has a completed status. Please see bellow:

SELECT COUNT(DISTINCT ID) 
FROM References 
WHERE ID NOT IN (
    SELECT DISTINCT ID 
    FROM References
    WHERE STATUS = 'COMPLETED'
)
AND STATUS ='FAILED'

Upvotes: 1

sgeddes
sgeddes

Reputation: 62851

Here's one option using conditional aggregation:

select count(*)
from (
  select id
  from refernce
  group by county, vendor, id
  having max(case when status = 'Failed' then 1 else 0 end) != 
         max(case when status = 'Completed' then 1 else 0 end)
  ) t

Here's a working example:

Upvotes: 0

Related Questions