0

I have the query

SELECT
   datecreate AS period,
   count(cod) as qt
FROM `tablename`
WHERE `date` between CURDATE() - INTERVAL -1 year and CURDATE()
GROUP BY period

that returns

+----------+-----+
|  period  | qt  |
+----------+-----+
|2015-10   |  5  |
+----------+-----+
|2015-11   |  7  |
+----------+-----+
|2016-06   |  9  | < --- the query ends here 
+----------+-----+
|2016-05   |  0  | < --- what i need
+----------+-----+
...
...
...

to fill de date with the months that not are matched on the data range ( and count 0 ), because the date 2016-11 and above do not exists in table

i usually do it on the server but i think that can do it by SQL

Israel Fasterra da Silva Desk
  • 1
  • Maybe you want to GROUP BY period, count(cod)? – Chuck Jun 13 '16 at 19:47
  • i want to fill the date column with other dates, like 2016-04,2016-03 until the where "CURDATE() - INTERVAL -1 year", the problem is that these dates do not exist on table – Israel Fasterra da Silva Desk Jun 13 '16 at 20:02
  • You might be able to union in all the missing dates, based on your date range, or something like that. – Chuck Jun 13 '16 at 20:10
  • For a row to be displayed you need to have it in the database. You can create a helpertable (either as a subquery or as a real table) that contains all month you want to display and left join them with your query. – Solarflare Jun 13 '16 at 20:50
  • if i do the query : if(date not in (1,2,3,4,5,6,7,8,9,10,11).'12',if(date not in (1,2,3,4,5,6,7,8,9,10),11,if(...)...)) works? – Israel Fasterra da Silva Desk Jun 13 '16 at 21:38
  • I'm not exactly sure what you mean by that. Maybe have a look here [MySQL how to fill missing dates in range?](http://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range) – Solarflare Jun 14 '16 at 17:15
  • Possible duplicate of [mysql group by month including empty months](http://stackoverflow.com/questions/8007363/mysql-group-by-month-including-empty-months) – Matt Raines Jun 14 '16 at 20:24

0 Answers0