case - How can I perform a COUNT on a subset of a population that has been defined by above in the SELECT clause by COUNT(DISTINCT())

Question

MYSQL

Attempting to filter first pass data and then count on that population. At this time I am able to retrieve the FIRST_PASS data, but I do not know how I can pass that data to the COUNT CASE.

Goal is to capture the PASS and FAIL data from the subset FIRST_PASS shown below.

EXECUTE THIS CODE

SELECT 
        COUNT(distinct(uut_serial_number)) AS FIRST_PASS,
        COUNT(CASE WHEN u.uut_status = 0 THEN u.id ELSE NULL END) AS FAIL,
        COUNT(CASE WHEN u.uut_status = 1 THEN u.id ELSE NULL END) AS PASS,
        COUNT(u.id) AS TOTAL_RAN,
        DATE_FORMAT(start_date_time, '%Y-%M-%d') AS DATE_RAN
    
FROM m3_module_pulse.result_uut u
WHERE u.program_name = 'THERMOGRAPHICPULSE' 
    AND u.start_date_time > NOW() - INTERVAL 30 DAY
GROUP BY DATE_RAN DESC

RETURNS

F/P F P Tot Date 220 74 165 239 2021-January-21 445 165 327 492 2021-January-20 602 210 438 648 2021-January-19 544 186 395 581 2021-January-18 501 236 340 576 2021-January-17 443 173 334 507 2021-January-16 468 153 340 493 2021-January-15

enter image description here

question from:https://stackoverflow.com/questions/65834953/how-can-i-perform-a-count-on-a-subset-of-a-population-that-has-been-defined-by-a

Answer 1

Waitting for answers