sql - Select count between range of date -

i count entries between 2 dates (the last week until today included) , if there none, select 0. prints :

+-------+------------+ | items | selldate   | +-------+------------+ | 1     | 2017-01-01 | +-------+------------+ | 3     | 2017-01-02 | +-------+------------+ | 1     | 2017-01-03 | +-------+------------+ | 5     | 2017-01-06 | +-------+------------+ 

however, need print this:

+-------+------------+ | items | selldate   | +-------+------------+ | 1     | 2017-01-01 | +-------+------------+ | 3     | 2017-01-02 | +-------+------------+ | 1     | 2017-01-03 | +-------+------------+ | 0     | 2017-01-04 | +-------+------------+ | 0     | 2017-01-05 | +-------+------------+ | 5     | 2017-01-06 | +-------+------------+ | 0     | 2017-01-07 | +-------+------------+ 

my query this:

select      count(item.id) items,     date(item.sold_at) selldate item item.sold_at not null , item.sold_at between date(datetime('now', 'localtime', '-6 days')) , date(datetime('now', 'localtime', '+1 day')) group selldate 

what doing wrong?

as far know not possible without recursive common table expression supported in sqlite 3.8.3 , higher. corresponding version, can joining date range items list:

with recursive dates(date) (   values(date(datetime('now', 'localtime', '-6 days')))   union   select date(date, '+1 day')   dates   date < date(datetime('now', 'localtime', '+1 day')) ) select      date,     count(item.id) items      dates left join     item on     dates.date = item.selldate group     selldate