Distinct count of 2 variables in SQL Server-CodePudding

My table has columns id and status (failed, success) and status_id. Failure entries are redundant until a success reponse is saved. How to fetch distinct success and failure count failure attempts to be counted only once?

Query I tried:

select 
    sum(case when status_id in (1,2,3,4) then 1 else 0 end) as success, 
    sum(case when status_id in (5,6,7,8) then 1 else 0 end) as failed
from test

This query works but failed attempts are counted twice

id	status_id	status
123	5	failed
123	1	success
123	7	failed
123	5	failed

desired output success - 1 failure - 2

CodePudding user response：

Counting the unique status_id's might work.

select 
 COUNT(DISTINCT case when status_id in (1,2,3,4) then status_id end) as success, 
 COUNT(DISTINCT case when status_id in (5,6,7,8) then status_id end) as failed
from test

CodePudding user response：

To count each success/fail only once, do select distinct in a derived table before aggregating:

select 
    sum(case when status_id in (1,2,3,4) then 1 else 0 end) as success, 
    sum(case when status_id in (5,6,7,8) then 1 else 0 end) as failed
from (select distinct status_id from test) dt