Adding all months in past year in mysql query result, even if no rows

Question

I am looking for a way to retrieve all subscriptions to vacancies over the past 12 months. Since not every vacancy will have a subscription in each month, some rows will have to return 0, but right now it's just not in the result set. How do I add the missing rows?

The tables I use are the following:

CREATE TABLE `calendar_months` (
  `month_id` int(8) DEFAULT NULL,
  `en_abbr` varchar(255) NOT NULL,
  `en_long` varchar(255) NOT NULL,
  `nl_abbr` varchar(255) NOT NULL,
  `nl_long` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `calendar_months` (`month_id`, `en_abbr`, `en_long`, `nl_abbr`, `nl_long`) VALUES
(1, 'jan', 'January', 'jan', 'Januari'),
(2, 'feb', 'February', 'feb', 'Februari'),
(3, 'mar', 'March', 'mrt', 'Maart'),
(4, 'apr', 'April', 'apr', 'April'),
(5, 'may', 'May', 'mei', 'Mei'),
(6, 'jun', 'June', 'jun', 'Juni'),
(7, 'jul', 'July', 'jul', 'Juli'),
(8, 'aug', 'August', 'aug', 'Augustus'),
(9, 'sep', 'September', 'sep', 'September'),
(10, 'oct', 'October', 'okt', 'Oktober'),
(11, 'nov', 'November', 'nov', 'November'),
(12, 'dec', 'December', 'dec', 'December');

CREATE TABLE `vacancies` (
  `vacancy_id` int(11) NOT NULL,
  `org_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL COMMENT 'title',
  `description` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0',
  `status` int(11) NOT NULL DEFAULT '0'COMMENT
) ;

CREATE TABLE `vacancy_subscriptions` (
  `subscription_id` int(11) NOT NULL,
  `vacancy_id` int(11) DEFAULT NULL,
  `user_id` int(10) DEFAULT NULL,
  `subscription_date` datetime NOT NULL,
  `message` text
)

I use the following query:

SELECT
  CONCAT(cm.nl_long, ' ', YEAR(v.create_time)) as label, YEAR(v.create_time) as vacyear, MONTH(v.create_time) as vacmonth, COUNT(*) as totalsubscriptions
FROM `calendar_months` as cm
LEFT JOIN `vacancies` as v on cm.month_id = month(v.create_time)
LEFT JOIN `vacancy_subscriptions` as vs on v.vacancy_id = vs.vacancy_id 
WHERE
  (v.create_time >= (DATE_ADD(NOW(),INTERVAL -12 MONTH)))
  AND v.is_deleted = 0
  AND v.org_id = 1
GROUP BY vacyear, vacmonth
ORDER BY vacyear ASC, vacmonth ASC

And this gives me the result I am looking for.

+---------------+---------+----------+--------------------+
|     label     | vacyear | vacmonth | totalsubscriptions |
+---------------+---------+----------+--------------------+
| Oktober 2017  |    2017 |       10 |                  5 |
| November 2017 |    2017 |       11 |                  1 |
| December 2017 |    2017 |       12 |                 13 |
| Maart 2018    |    2018 |        3 |                  4 |
| April 2018    |    2018 |        4 |                  5 |
+---------------+---------+----------+--------------------+

But how do I add the months with no subscriptions in the past 12 months with the last column just being "0"? Like this:

+----------------+---------+----------+--------------------+
|     label      | vacyear | vacmonth | totalsubscriptions |
+----------------+---------+----------+--------------------+
| Mei 2017       |    2017 |        5 |                  0 |
| Juni 2017      |    2017 |        6 |                  0 |
| Juli 2017      |    2017 |        7 |                  0 |
| Augustus 2017  |    2017 |        8 |                  0 |
| September 2017 |    2017 |        9 |                  0 |
| Oktober 2017   |    2017 |       10 |                  5 |
| November 2017  |    2017 |       11 |                  1 |
| December 2017  |    2017 |       12 |                 13 |
| Januari 2018   |    2018 |        1 |                  0 |
| Februari 2018  |    2018 |        2 |                  0 |
| Maart 2018     |    2018 |        3 |                  4 |
| April 2018     |    2018 |        4 |                  5 |
+----------------+---------+----------+--------------------+

UPDATE: I have created an SQL fiddle containing some data! http://www.sqlfiddle.com/#!9/75db76

Answer 1

Move your where condition related to left join table in the on clause

SELECT
  CONCAT(cm.nl_long, ' ', YEAR(v.create_time)) as label, YEAR(v.create_time) as vacyear, ifnull(MONTH(v.create_time),0) as vacmonth, ifnull(COUNT(*),0) as totalsubscriptions
FROM `calendar_months` as cm
LEFT JOIN `vacancies` as v on cm.month_id = month(v.create_time) 
    AND  (v.create_time >= (DATE_ADD(NOW(),INTERVAL -12 MONTH)))
    AND v.is_deleted = 0
    AND v.org_id = 1
LEFT JOIN `vacancy_subscriptions` as vs on v.vacancy_id = vs.vacancy_id 
GROUP BY vacyear, vacmonth, label
ORDER BY vacyear ASC, vacmonth ASC

otherwise the where condition work as an inner join and don't return rows if the value don't match

do the fact you have not the year for all the month you could use a cross join on union for get this value

SELECT
  CONCAT(cm.nl_long, ' ', y.my_year) as label, YEAR(v.create_time) as vacyear
    ,  ifnull(MONTH(v.create_time), 0) as vacmonth, ifnull(COUNT(*),0) as totalsubscriptions
FROM `calendar_months` as cm
CROSS JOIN (
    select 2018 my_year
    from dual 
    union 
    select 2017 
    from dual 
) y 
LEFT JOIN `vacancies` as v on cm.month_id = month(v.create_time) 
    AND  (v.create_time >= (DATE_ADD(NOW(),INTERVAL -12 MONTH)))
    AND v.is_deleted = 0
    AND v.org_id = 1
LEFT JOIN `vacancy_subscriptions` as vs on v.vacancy_id = vs.vacancy_id 
GROUP BY vacyear, vacmonth, label
ORDER BY y.my_year ASC, vacmonth ASC

Answer 2

Your logic is broken. Your calendar table needs to include both the month and the year -- and the first date of the month (for convenience).

Then you can do:

SELECT CONCAT(cm.nl_long, ' ', cm.year) as label, COUNT(*) as totalsubscriptions
FROM calendar_months cm LEFT JOIN
     vacancies v 
     ON cm.month_id = month(v.create_time) AND
        cm.year = year(v.create_time) AND
        v.is_deleted = 0 AND
        v.org_id = 1 LEFT JOIN
     `vacancy_subscriptions` vs
     on v.vacancy_id = vs.vacancy_id 
WHERE cm.month_start_date >= DATE_ADD(NOW(), INTERVAL -12 MONTH))
GROUP BY label
ORDER BY MIN(cm.month_start_date);

Notes:

• The filtering on all but the first table should be in the ON clauses (for LEFT JOIN.
• The GROUP BY needs to be on fields in the first table.
• If you have a real calendar table (rather than just a list of months), then you can filter for the past year on that table.
• You probably don't want to filter on NOW() -- you'll get 13 months of data, with partial first and last months.