CODEWITHSUNDEEP
mysql
androidnation
androidnation

Reputation: 636

Count patients for each month

I am trying to count patient in each month, and I need to see the result for each month number even if it is 0.

The query that I am running should give 13 on month number 3 and 0 on other months. But what is happening is the number 13 is divided and distributed into all 12 months like the following:

enter image description here

And here is the query:

SELECT 
    count(patients) as patients, month

FROM 
(
    SELECT 
        count(app_id) as patients, 
        month(date_app) as month 
    FROM 
        dentist.appointment
    WHERE id_logged=1 AND year(date_app)='2016'
    GROUP BY month(date_app)

    UNION SELECT 0,1
    UNION SELECT 0,2
    UNION SELECT 0,3
    UNION SELECT 0,4
    UNION SELECT 0,5
    UNION SELECT 0,6
    UNION SELECT 0,7
    UNION SELECT 0,8
    UNION SELECT 0,9
    UNION SELECT 0,10
    UNION SELECT 0,11
    UNION SELECT 0,12
) tmp
GROUP BY month

Upvotes: 1

Views: 211

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try this query:

SELECT 
    count(app_id) as patients, 
    t.m as month 
FROM (    
   SELECT 1 AS m UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
   SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION
   SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12) AS t
LEFT JOIN dentist.appointment ON t.m = month(date_app) AND
          id_logged = 1 AND year(date_app) = 2016
GROUP BY t.m

The trick is first create an in-line table containing all 12 month numbers, then LEFT JOIN your original table to the in-line table.

Note: Predicates id_logged = 1 and year(date_app) = 2016 should be placed in the ON clause of the LEFT JOIN operation, otherwise LEFT JOIN becomes an INNER JOIN.

Demo here

Upvotes: 1

Related Questions