CODEWITHSUNDEEP
mysql
Raccoon
Raccoon

Reputation: 1427

Mysql - filling rows for missing months

Gosh, it must be so simple but I'm struggling with this 'filling out missing data' issue.

I have a table that has the following columns with some inserted data.

TABLE

year  month  payment
2014    3      100
2014    5      800
2014    9      200

And what I want from this table is to have a full range of months with its payment value from 2014.

Month    Payment
  1        0
  2        0
  3       100
  4        0
  5       800

  ...

  12       0

I tried using IFNULL in select but failed so bad... and search results from stackoverflow usually join two or more tables to manipulate information. What would be the fastest and best solution to solve this problem?

Upvotes: 3

Views: 3074

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

For missing months you can have a union query with all months and join with your table

SELECT 
t1.`year`,
t.`month`,
coalesce(t1.payment,0) payment
FROM
(SELECT 1 AS `month`
UNION 
SELECT 2 AS `month`
UNION 
....
SELECT 12 AS `month`
) AS t
LEFT JOIN your_table t1 on(t.`month` = t1.`month`)
WHERE ....

Fiddle Demo

Upvotes: 4

Related Questions