WITH a AS ( SELECT orgguid, SUM(CASE WHEN CodeFunction = '001' THEN 1 ELSE 0) AS sum_001, SUM(CASE WHEN CodeFunction = '001001' THEN 1 ELSE 0) AS sum_001001, SUM(CASE WHEN CodeFunction = '001002' THEN 1 ELSE 0) AS sum_001002 FROM tbl GROUP BY OrgGuid )SELECT orgguid FROM a WHERE sum_001 > 0 AND SUM_001001 > 0 AND SUM_001002 > 0
SELECT orgguid FROM tbl GROUP BY OrgGuid HAVING SUM(CASE WHEN CodeFunction = '001' THEN 1 ELSE 0 END)>0 AND SUM(CASE WHEN CodeFunction = '001001' THEN 1 ELSE 0 END)>0 AND SUM(CASE WHEN CodeFunction = '001002' THEN 1 ELSE 0 END)>0
SELECT orgguid,
SUM(CASE WHEN CodeFunction = '001' THEN 1 ELSE 0) AS sum_001,
SUM(CASE WHEN CodeFunction = '001001' THEN 1 ELSE 0) AS
sum_001001,
SUM(CASE WHEN CodeFunction = '001002' THEN 1 ELSE 0) AS
sum_001002
FROM tbl
GROUP BY
OrgGuid
)SELECT orgguid
FROM a
WHERE sum_001 > 0
AND SUM_001001 > 0
AND SUM_001002 > 0
SELECT orgguid
FROM tbl
GROUP BY OrgGuid
HAVING
SUM(CASE WHEN CodeFunction = '001' THEN 1 ELSE 0 END)>0 AND
SUM(CASE WHEN CodeFunction = '001001' THEN 1 ELSE 0 END)>0 AND
SUM(CASE WHEN CodeFunction = '001002' THEN 1 ELSE 0 END)>0