mysql select default value for date if no results

Question

I have a payments table grouped by day

select
 day(created),
 sum(payments.amount) 
from payments
group by day(created)

output

day | amount
1   | 432
2   | 4567
5   | 345
6   | 2345
7   | 97

which is fine, but i'd like to put some 0 in the days which did not have payments.

expected output:

day | amount
1   | 432
2   | 4567
3   | 0
4   | 0
5   | 345
6   | 2345
7   | 97

Possible duplicate of [Mysql group by weekday, fill in missing weekdays](http://stackoverflow.com/questions/40403859/mysql-group-by-weekday-fill-in-missing-weekdays) — Paul Spiegel, Feb 17 '17 at 22:38
Check this answer: http://stackoverflow.com/a/8888404/1040225 — konrad_pe, Feb 17 '17 at 22:39

mechanical_meat · Answer 1 · 2017-02-18T00:19:47.860

0

Since you don't have all the dates in the payments table you can do:

select
    d,
    ifnull(sum(p.amount),0) sum_amount
from (
         select 1 as d
         union all 
         select 2
         union all 
         select 3
         union all 
         select 4
         union all 
         select 5
         union all 
         select 6
         union all 
         select 7
    ) d
    left join payments p 
    on day(p.created) = d.d
group by d
order by d

Sample here: http://sqlfiddle.com/#!9/cb09d1/2

edited Feb 18 '17 at 00:19

answered Feb 17 '17 at 22:49

mechanical_meat

163,903
24
228
223

no, i don't have all the dates in the payments table, but i need to return them as though they do if they're empty – Blair Anderson Feb 18 '17 at 00:07
@BlairAnderson: based on your comment please see edited answer. – mechanical_meat Feb 18 '17 at 00:12

score 0 · Answer 2 · answered Feb 18 '17 at 01:50

You may not have the data for certain days in your table, thus you can't ask the engine to return you that. Simply can't, it basically knows nothing about the human's calendar :-)

So you have to either retrospectively generate stub records with zeroes for these days, or use the method @bernie suggested, this particular way, or materialized in permanent table holding days from 1 to 31.

mysql select default value for date if no results

2 Answers2