Difficulty visualizing a solution for simple problem

Transact-SQL
1

Hi - I am building a query (Stored Procedure) against a single table. The objective is to return statistics on technicians that are testing new products. Each product is tested and if it passes it is shipped out the door. If the product fails it goes back for software reload. Each test records the product's serial number. In the table a serial number will only appear once for a passed test, however the serial number could appear multiple times when the test is failed. What I want to see as a result set is the total number of devices tested in a given date range, the number of devices passed, and the number of the devices that failed. If a serialed device failed more than once I only want it counted once. Can anyone assist?
My query:

    SELECT C_UserName, Count(t.id) as Total, 
SUM(CASE WHEN C_STATUS = 0 Then 1 ELSE 0 END) as Pass,
(CASE WHEN C_STATUS <> 0 Then 1 ELSE 0 END) as Fail -- only want to count first failure
from TableResMLT t 
Where C_SITE like 'SH%'
and (t.DateEntered >= @DateFrom and t.DateEntered < DateAdd(day, 1, @DateTo))
Group by C_Username
2

Something like this. The column names etc. may be wrong (e.g., you refer to serial number in the text, but there is no serial number in the query.

SELECT
	COUNT(DISTINCT serial_number) AS DevicesTested,
	SUM(CASE WHEN C_STATUS = 0 THEN 1 ELSE 0 END) AS Passed,
	SUM(DISTINCT CASE WHEN C_STATUS<> 0 THEN 1 ELSE 0 END) AS Failed
FROM
	Yourtable....
WHERE
	YourWhereconditions...
3

Thank-you, I'll give that a go.

4

Alternative to:

	COUNT(DISTINCT serial_number)
	-SUM(CASE WHEN C_STATUS = 0 THEN 1 ELSE 0 END) AS Passed
5

Thank-you for the response. The query is working.