Select only where all rows for a “customer” have the same status?
I have a status table with a bit field closed
I want to try and select all CustomerNumbers where ALL of the rows for that customer are closed
The following works...but I'm sure there is a more sensible way of doing it!
SELECT * FROM (SELECT lcs.CustomerNumber AS CustomerNumber , COUNT(lcs.CustomerNumber) AS Total FROM Status lcs GROUP BY lcs.CustomerNumber) total LEFT JOIN (SELECT lcs.CustomerNumber AS CustomerNumber , COUNT(lcs.CustomerNumber) AS Closed FROM Status lcs WHERE lcs.Closed = 1 GROUP BY lcs.CustomerNumber) closed ON closed.CustomerNumber = total.CustomerNumber WHERE closed.Closed = total.Total
Each customer can have one or more rows, each either closed=0 or closed=1
I need to select only when ALL of the rows for the customer are closed.
CustomeNumber Closed 111 0 111 0 112 1 112 0 113 1 113 1 114 1
This should select: 113 and 114 Both have ALL of their status's as Closed.
SELECT customerNumber FROM TableName GROUP BY customerNumber HAVING COUNT(*) = SUM(CASE WHEN closed = 1 THEN 1 END)
╔════════════════╗ ║ CUSTOMERNUMBER ║ ╠════════════════╣ ║ 113 ║ ║ 114 ║ ╚════════════════╝
Another way of solving it would be:
SELECT CustomerNumber FROM Status WHERE closed = 1 EXCEPT SELECT CustomerNumber FROM Status WHERE closed = 0
The values returned from EXCEPT will be distinct so no need to add that.