Set Value Based on Number of Records with Certain Values using SQL Server

Question

I am stuck on how to return a bit value based on records with certain values. Query needs to return TRUE or FALSE if there are three different Levels of the same Control:

Example data:

PRODUCT    CONTROL    LEVEL
test1      8          1
test1      8          2
test1      8          3
test2      8          1
test2      8          1
test2      8          3
test4      8          1
test4      8          3
test4      8          7

I need a query that would return:

PRODUCT    STATUS
test1      PASS
test2      FAIL
test4      PASS

Not sure where to begin. Any tips are greatly appreciated.

Answer 1

This query will return PASS if there are at least three unique levels, otherwise FAIL.

SELECT PRODUCT, (CASE
       WHEN COUNT(DISTINCT [LEVEL])>=3 THEN 'PASS'
       ELSE 'FAIL' END) AS [STATUS]
FROM tablenamegoeshere
GROUP BY PRODUCT, [CONTROL];

COUNT(DISTINCT ..) counts the number of unique values of a column or expression. CASE returns different values depending on the outcome of different conditions. In this case, if the number of unique values is equal to or greater than three, we want to return 'PASS' otherwise 'FAIL'.

Obviously, if you want to return a bit value (i.e. boolean), you could change the strings 'PASS' and 'FAIL' in the code into 1 and 0 respectively and cast them to bit using CAST(... AS bit).

Answer 2

WITH ctePassFail(Product,Control,ControlCount)
AS
(   
    SELECT PRODUCT, CONTROL, COUNT(DISTINCT LEVEL)
    FROM ProductTest
    GROUP BY PRODUCT, CONTROL
)
SELECT Product, CASE WHEN ControlCount >= 3 'PASS' ELSE 'FAIL' END STATUS
FROM ctePassFail

Answer 3

To add to the other answers, this query gives PASS or FAIL for each unique Product having 1 or more Controls with at least 3 different Levels for one at least one of the Controls:

SELECT PRODUCT
    , CASE WHEN MAX(total) >= 3 THEN 'PASS' ELSE 'FAIL' END
FROM (
    SELECT PRODUCT, total = COUNT(DISTINCT LEVEL)
    FROM data
    GROUP BY PRODUCT, CONTROL
) as g
GROUP BY PRODUCT
;