Hi List,
Let's say I have a table with a simplified structure like this:
/ create table my_table(//
// time_date DATE,//
// num INTEGER//
// );/
My data has three rows and looks like this:
/ 2016-01-01; 3//
// 2016-01-01; 1//
// 2016-01-03; 2/
I want to return the sum of "num" for any given time_date, grouped
by day, so I have:
/ SELECT time_date,//
// sum( num ) AS sum_num//
// FROM my_table//
//
// GROUP BY time_date//
// ORDER BY time_date ASC/
This returns:
/ 2016-01-01; 4//
// 2016-01-03; 2/
But what I want is for a value of "0" to be returned for the
"missing" date 2016-01-02:
/ 2016-01-01; 4//
// 2016-01-02; 0//
// 2016-01-03; 2/
Is this possible? I'm imagining a sub-query with a start and end
date ("now") but I can't think of how to get SQLite to return data that
it doesn't have.
Does anyone have any suggestions for how to achieve this?
Thanks,
Jonathan
