SQL query count customers-CodePudding

Maybe this is a silly question but have a black hole in my head... so I just need count how many suppliers we have with status CLOSED or READY FOR AUDIT, but in the same time when any of those suppliers have status "NULL" I don't want to count them.

JUSTRITE MANUFACTURING COMPANY  CLOSED
JW SPEAKER CORPORATION          CLOSED
KLEIN TOOLS INC                 NULL
KLEIN TOOLS INC                 CLOSED
KLEVER INNOVATIONS              CLOSED
LA-CO INDUSTRIES INC            CLOSED

========================================================================

With this example We can see 5 different customers, but the results will be "4" because KLEINT contains NULL and I don't want him.

CodePudding user response：

Let's say table is defined like the following:

Table name: your_table with two fields

supplier          status    
----------------  ------

Then you can get the result by aggregation function count.

SELECT 
   supplier,
   SUM(IFNULL((SELECT 1 FROM your_table WHERE a. supplier = supplier LIMIT 1), 0)) AS cnt
FROM your_table a
WHERE `status` IN ('CLOSED', 'READY')
GROUP BY supplier
HAVING cnt > 0
;

CodePudding user response：

I have made a fiddle for you with the solution as an example

https://www.db-fiddle.com/f/rW914i1yUA5GBK2d9j6PGB/0

also the code should look like this

SELECT count(*)
FROM test
WHERE STATUS IS NOT NULL
    AND STATUS IN (
        'CLOSED'
        ,'READY FOR AUDIT'
        )

this code ignores NULL values, (but they have to be empty values, not someone typing NULL in the fields. that makes a diffrence)

CodePudding user response：

A simple subquery will do it here:

SELECT COUNT(DISTINCT supplier) 
FROM tab 
WHERE 
    status IN ('CLOSED', 'READY FOR AUDIT')
AND 
    supplier NOT IN (SELECT supplier FROM tab WHERE status IS NULL)

Here's the fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=c2dd8f7672ecca3a81d6587588d44226.

CodePudding user response：

WITH CTE(COMPANY,STATUSS) AS
(
   SELECT  'JUSTRITE MANUFACTURING COMPANY',  'CLOSED' UNION ALL
   SELECT'JW SPEAKER CORPORATION',          'CLOSED' UNION ALL
   SELECT'KLEIN TOOLS INC' ,                NULL UNION ALL
   SELECT'KLEIN TOOLS INC' ,                'CLOSED' UNION ALL
   SELECT'KLEVER INNOVATIONS',              'CLOSED' UNION ALL
   SELECT'LA-CO INDUSTRIES INC'  ,          'CLOSED'
)
SELECT COUNT(C.COMPANY)
FROM CTE AS C
WHERE C.STATUSS IN('CLOSED','READY FOR AUDIT')
AND NOT EXISTS
(
   SELECT 1 FROM CTE AS X WHERE C.COMPANY=X.COMPANY AND X.STATUSS IS NULL
)

CTE is an example of your data. Please replace it with your table name