COUNT DISTINCT if sum is zero-CodePudding

Need help to count the values in column A if sum of column B equals 0 group by column A.

SELECT    
A, SUM(B) --???   
FROM table  
WHERE DATE = 'yyyy-mm-dd'   
AND B = 0  
GROUP BY A

CodePudding user response：

A number of A's which sum(B)=0.

select count(*)A
from (
    SELECT A
    FROM table  
    WHERE DATE = ... -- your date here
    GROUP BY A 
    HAVING SUM(B)=0
) t

CodePudding user response：

select count(Distinct A) 
from (
    SELECT sum(B) as B, A
    FROM table  
    GROUP BY A 
    ) a
 Where B = 0 and DATE = 'yyyy-mm-dd'

CodePudding user response：

It's working now. Thankyou all for sharing solutions.

select count(*) MACHINE_CODE  
from (
    SELECT MACHINE_CODE  
    FROM CNC_hourly_logging chl  
    WHERE C_DATE ='2022-01-28'  
    AND cast(convert(char(8), [C_TIME], 108) as time) BETWEEN cast('08:00' as time) AND cast('18:30' as time)  
    GROUP BY MACHINE_CODE  
    HAVING SUM(TOTAL_PRODUCTION)=0  
) t