Search code examples
sqlsql-serversql-query-store

Count rows with specific data only, excluding data if it has another record with different details in another column

I have 3 columns in my table tbl_Result such as: id, Serial and Result, in Serial column data can be duplicate if it has pass and fail results, I want to select the Serial that has Pass only meaning don't include the Serial with fail even it has a pass.

as you can see in my data Serial A1 and A5 has pass and fail results I don't want to include that in my select query

I tried this code below but I'm still selecting the A1 and A5 Serial, the total records I am expecting should be 8 only.

Select Serial, Result from tbl_Result where Result='pass' and Result not in('fail')
group by Serial, Result
having count(Serial)<2

Thanks in advance.

Solution

  • Looks like you need conditional aggregation. You are also grouping by too many columns.

    SELECT
  Serial
FROM tbl_Result
GROUP BY
  Serial
HAVING COUNT(CASE WHEN Result = 'Fail' THEN 1 END) = 0;