CODEWITHSUNDEEP
sql-servert-sqlselect
ImmortalStrawberry
ImmortalStrawberry

Reputation: 6091

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.

Sample:

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.

Upvotes: 2

Views: 274

Answers (2)

David Abrahamsson
David Abrahamsson

Reputation: 326

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.

Upvotes: 3

John Woo
John Woo

Reputation: 263723

SELECT  customerNumber
FROM    TableName
GROUP   BY customerNumber
HAVING  COUNT(*) = SUM(CASE WHEN closed = 1 THEN 1 END)

OUTPUT

╔════════════════╗
║ CUSTOMERNUMBER ║
╠════════════════╣
║            113 ║
║            114 ║
╚════════════════╝

Upvotes: 3

Related Questions