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.

Answers


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

OUTPUT

╔════════════════╗
║ 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.


Need Your Help

RecursiveIterator with directories in PHP

php recursion recursiveiterator

I want to list directories and files in directories like:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.