substitude zeros for missing months in mysql query

Question

I have a query that gets the data between two date ranges.

    SELECT  
            MONTH(ENC_DT)         as Month, 
            monthname(ENC.ENC_DT) as Month_Name,
            year(ENC.ENC_DT)      as Year,
            ENC.ENC_DT,
            ENC.ENC_ID

    FROM
            IDR_ENC ENC

    where                   
            DATE_FORMAT(ENC.ENC_DT, '%Y-%m-%d') >= '2014-01-01' and 
            DATE_FORMAT(ENC.ENC_DT, '%Y-%m-%d') <= '2014-10-16' 
    GROUP BY Month_Name, Year

I need it to return zeros for those months that are missing in between specified date range.

Is this doable? I checked every website I could find but nothing that could help me.

possible duplicate of [MySQL how to fill missing dates in range?](http://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range) — Nanne, Oct 16 '14 at 20:29

O. Jones · Accepted Answer · 2014-10-16T20:34:43.847

I assume you are asking for rows in your result set corresponding to the months in which you have no data in your table.

First of all, with respect, you are misusing a MySQL extension to GROUP BY. Please read this. http://dev.mysql.com/doc/refman/5.6/en/group-by-extensions.html It's hard for us, and for the server, to guess what you want when you misuse that extension.

Third, you don't need the DATE_FORMAT() stuff in your WHERE clause. Dates can be compared directly.

Third, you need a table of all the numbers from 1 to 12. This is easy to generate in a simple query, as follows. We'll use that later on.

        SELECT 1 AS seq 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

Fourth, you need to join your list of numbers to your other table.

SELECT seq.seq            as Month, 
       MONTHNAME(CONCAT( YEAR(NOW()), '-', seq.seq, '-01')) as Month_Name,
       year(ENC_DT)       as Year
       COUNT(*)           as ENC_Record_Count
FROM  (
        SELECT 1 AS seq 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 seq 
LEFT JOIN IDR_ENC AS ENC ON seq.seq = MONTH(ENC.ENC_DT)
where ENC.ENC_DT >= '2014-01-01'  
and   ENC.ENC_DT  <= '2014-10-16' 
group by year(ENC.ENC_DT),seq.seq
order by year(ENC.ENC_DT),seq.seq

This will work as long as you only select dates lying within a single calendar year -- we only have twelve months' worth of integers.

There's some tricky stuff to get the month name from the integer.

For a more elaborate discussion of this kind of query, please see this. http://www.plumislandmedia.net/mysql/filling-missing-data-sequences-cardinal-integers/

substitude zeros for missing months in mysql query

1 Answers1

Related