SQL - Determine if Column in Group are all the same value

Question

I have a SQL table that is grouped by serial numbers, with a secondary data column. Per serial number, I'd like to count if the data column has all equivalent values of a specific values For example

Serial#	Data
1	pass
1	pass
2	pass
2	fail
3	fail
3	fail
3	fail

I'd have a count of "1" for serial#1, but 2 and 3 would be "0"

Answer 1

You can "sum" to obtain the success:

SELECT 
    [Serial#], 
    1 + Sgn(-Abs(Sum([Data] <> 'pass'))) AS Success
FROM 
    SerialTable
GROUP BY 
    [Serial#];

or, using Min as suggested by Mat:

SELECT [Serial#], Min(Abs([Data] = 'pass')) AS Success
FROM SerialTable
GROUP BY [Serial#];