generate empty rows even if empty between 2 dates

Question

I have this simple query that fetches all completed orders between 2 dates(about 6 months at a time):

select IFNULL(sum(o.amount), 0) as amount, o.completed_at 
from orders o 
where date(o.completed_at) between '2011/10/01' and '2012/03/06' 
group by year(o.completed_at), month(o.completed_at) order by o.completed_at

But the problem here is for example in Jan and Feb, if there are no orders, then this will only return 4 rows instead of 6 rows for 6 months.

I need it still return 6 rows and just set the amount to 0 for those missing dates.

Here is a sample output that only returns 5 rows - March is missing i need it to appear: http://d.pr/DEuR

This might be of help: http://stackoverflow.com/a/9552735/268273 — Mosty Mostacho, Mar 06 '12 at 02:23

Korhan Ozturk · Answer 1 · 2012-03-05T23:06:14.807

0

Try with coalesce function which will return '0' if the sum(orders.amount) is null for a specific month:

select COALESCE(sum(o.amount), 0) as amount, o.completed_at 
from orders o 
where date(o.completed_at) between '2011/10/01' and '2012/03/06' 
group by year(o.completed_at), month(o.completed_at) order by o.completed_at

edited Mar 05 '12 at 23:06

answered Mar 05 '12 at 22:59

Korhan Ozturk

11,148
6
36
49

`COALESCE()` has the same function as `IFNULL()` as used by the OP. Unfortunately, it will not solve the issue of the missing rows. – Marcus Adams Mar 06 '12 at 01:10
How about using case-when clause to display '0' when there is null value? Would that work? – Korhan Ozturk Mar 06 '12 at 09:26

score 0 · Answer 2 · edited Mar 06 '12 at 14:07

0

Here is what you need:

1] create an auxiliary table

 create table db.myDates(
   completed_at date not null
 )

2] fill the table with the months in range, for example:

 insert into db.myDates values('2011-10-01'),
 ('2011-11-01'),
 ('2011-12-01'),
 ('2012-01-01'),
 ('2012-02-01'),
 ('2012-03-01');

3] then select:

 select a.completed_at, sum(b.amount)
 from myDates a
 left join orders b 
   on extract(year_month from a.completed_at)=extract(year_month from b.completed_at)
     and date(b.completed_at) between '2012-01-05' and '2012-06-05'
 group by year(b.completed_at)), month(dateCompleted)
 order by a.completed_at;

the result looks like this (dates mine)

2012-01-01  27
2012-02-01  NULL
2012-03-01  47
2012-04-01  13
2012-05-01  12
2012-06-01  15

edited Mar 06 '12 at 14:07

Marcus Adams

53,009
9
91
143

answered Mar 06 '12 at 01:31

tribal

653
2
13
26

can it be a temporary table? or after every sql i drop the table? – David Mar 06 '12 at 13:25
@David, the table would be different for each date range, so a temporary table seems like a good approach. – Marcus Adams Mar 06 '12 at 14:15
You have a few choices as far as the table goes: 1) it can be temporary; 2) it can be permanent and you just change the range each time; 3) it can be permanent and fill it with some three years of months so you don't have to change every six months. You are free to keep and maintain the table however you wish. – tribal Mar 06 '12 at 18:58

score 0 · Answer 3 · answered Mar 06 '12 at 01:50

0

This query is complicated due to a number of factors.

The easiest solution might be to add a 0 amount on the first of each month.

answered Mar 06 '12 at 01:50

Marcus Adams

53,009
9
91
143

generate empty rows even if empty between 2 dates

3 Answers3

Linked