CODEWITHSUNDEEP
mysql
GeekTantra
GeekTantra

Reputation: 12081

MySQL Group By Count with Zero

I have the following query:

SELECT apdr.patient_id,
COUNT(apdr.patient_id)
AS x_ray_result 
FROM apdr
WHERE apdr.type_of_test IN ('x_ray_opinion_1', 'x_ray_opinion_2') 
AND apdr.interpretation LIKE 'kochs' 
GROUP BY apdr.patient_id

It returns the count for number of patients who have interpretation as 'kochs'. How can I modify this to get the count as 0 also with the corresponding patient_id in the output.

Any help is appreciated.

I get counts starting from 1, I need the query where I get 0 as counts also.

Upvotes: 1

Views: 2035

Answers (3)

Krunal
Krunal

Reputation: 2193

select distinct a.patient_id,if(b.x_ray_result is null,0,b.x_ray_result ) as x_ray_result from apdr a left join (SELECT patient_id,COUNT(patient_id) AS x_ray_result FROM apdr WHERE type_of_test IN ('x_ray_opinion_1', 'x_ray_opinion_2') AND interpretation LIKE 'kochs' GROUP BY patient_id ) b on a.patient_id=b.patient_id;

Upvotes: 0

damienix
damienix

Reputation: 6753

Try

SELECT a1.patient_id, 
    (SELECT COUNT(*) FROM apdr a2 
    WHERE a2.interpretation LIKE 'kochs' 
    AND a1.patient_id=a2.patient_id) AS x_ray_result 
FROM apdr a1
WHERE a1.type_of_test IN ('x_ray_opinion_1', 'x_ray_opinion_2')
GROUP BY a1.patient_id

Edit: I've forgotten WHERE clause

Upvotes: 1

a'r
a'r

Reputation: 36999

Rather than putting the condition in the where clause, you can use a case statement to return a conditional count. See:

SELECT apdr.patient_id, 
    COUNT(CASE WHEN apdr.type_of_test IN ('x_ray_opinion_1', 'x_ray_opinion_2') 
                    AND apdr.interpretation LIKE 'kochs' 
               THEN adpr.patient_id 
          END CASE) AS x_ray_result 
FROM apdr
GROUP BY apdr.patient_id

Upvotes: 2

Related Questions