MySQL get all the months of the year stats

Question

I have to do a SQL query for getting the incomes of a company on all the months of the year, but some months dont have records in the table.

I have used this query:

SELECT COUNT(wp_dgl_stats.datetime_stamp)*wp_dgl_ads.price as incomes, MONTHNAME(wp_dgl_stats.datetime_stamp) as month
FROM wp_dgl_ads
INNER JOIN wp_dgl_stats ON wp_dgl_stats.id_ad = wp_dgl_ads.id
WHERE YEAR(wp_dgl_stats.datetime_stamp) = 2015
GROUP BY MONTHNAME(wp_dgl_stats.datetime_stamp)

I have to say that wp_dgl_stats contains a record for every click made by an user in certain spaces of the web (the ads showed) with a reference to the ad and a datetime stamp.

This query returns exactly months with incomes and the exact amount. But I need to get also the rest of the months with a 0.

How could this be done?

Cannot do that, that means change the full source of the platform, and may change the stats of all. Not an option. — Programador Adagal, Oct 19 '15 at 11:47
`LEFT/RIGHT` join with a set containing the numbers 1 through 12 (or 0 through 11 if your month numbers are 0 based). http://stackoverflow.com/questions/10922064/how-do-i-get-a-list-of-numbers-in-mysql — Klas Lindbäck, Oct 19 '15 at 12:01
Could you give me an example? I dont sure if I understand properly. — Programador Adagal, Oct 19 '15 at 12:03

score 10 · Accepted Answer · answered Oct 19 '15 at 15:36

After a lot of tests I got a proper solution. I will post it here if someone needs for it with an explanation.

SELECT meses.month, CAST(COUNT(stats.id)*ads.precio AS UNSIGNED) as precio
            FROM
            (
                      SELECT 1 AS MONTH
                       UNION SELECT 2 AS MONTH
                       UNION SELECT 3 AS MONTH
                       UNION SELECT 4 AS MONTH
                       UNION SELECT 5 AS MONTH
                       UNION SELECT 6 AS MONTH
                       UNION SELECT 7 AS MONTH
                       UNION SELECT 8 AS MONTH
                       UNION SELECT 9 AS MONTH
                       UNION SELECT 10 AS MONTH
                       UNION SELECT 11 AS MONTH
                       UNION SELECT 12 AS MONTH
            ) as meses
            LEFT JOIN wp_dgl_stats stats ON meses.month = MONTH(stats.datetime_stamp)
            LEFT JOIN wp_dgl_ads ads ON stats.id_ad = ads.id AND YEAR(stats.datetime_stamp) = '2015'
            GROUP BY meses.month

Because I am a spanish developer and I need to have spanish month names I selected the month number and with an PHP array we can convert the month number to his spanish name.

If someone have some question, do it, I will be glad to answer.

score 1 · Answer 2 · answered Oct 19 '15 at 13:50

You could LEFT/RIGHT join on the months:

WITH monthnames AS (SELECT <something that returns a list of the month names> as monthname)
SELECT COALESCE(COUNT(wp_dgl_stats.datetime_stamp)*wp_dgl_ads.price, 0) as incomes, MONTHNAME(wp_dgl_stats.datetime_stamp) as month
FROM wp_dgl_ads
INNER JOIN wp_dgl_stats ON wp_dgl_stats.id_ad = wp_dgl_ads.id
RIGHT JOIN monthnames ON month = monthname
WHERE YEAR(wp_dgl_stats.datetime_stamp) = 2015
GROUP BY MONTHNAME(wp_dgl_stats.datetime_stamp)

MySQL get all the months of the year stats

2 Answers2