On 2016/05/12 12:20 AM, Jonathan wrote:
> 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?
A simple CTE can do this as follows: (Note - it can be done with less
iterations, but this example makes clear what is happening)
-- SQLite version 3.9.2 [ Release: 2015-11-02 ] on SQLitespeed
version 2.0.2.4.
-- Script Items: 4 Parameter Count: 0
-- 2016-05-12 00:53:22.084 | [Info] Script Initialized,
Started executing...
--
================================================================================================
CREATE TABLE my_table(
time_date NUMERIC,
num INTEGER
);
INSERT INTO my_table VALUES
('2016-01-01 00:00:00', 3),
('2016-01-01 00:00:00', 1),
('2016-01-03 00:00:00', 2);
-- Without CTE:
SELECT time_date, sum( num ) AS sum_num
FROM my_table
GROUP BY time_date
ORDER BY time_date ASC
;
-- time_date | sum_num
-- --------------------- | -------
-- 2016-01-01 00:00:00 | 4
-- 2016-01-03 00:00:00 | 2
-- With CTE:
WITH dtRange(minDT,maxDT) AS (
SELECT MIN(time_date), MAX(time_date) FROM my_table
), dtAll(dt) AS (
SELECT minDT FROM dtRange
UNION ALL
SELECT datetime(dt,'+1 day') FROM dtAll,dtRange WHERE dt < maxDT
), my_full(time_date, num) AS (
SELECT time_date, num FROM my_table
UNION ALL
SELECT DT, 0 FROM dtAll
)
SELECT date(time_date) as time_date, sum(num) AS sum_num
FROM my_full
GROUP BY time_date
ORDER BY time_date ASC
;
-- time_date | sum_num
-- ------------ | -------
-- 2016-01-01 | 4
-- 2016-01-02 | 0
-- 2016-01-03 | 2
Cheers!
Ryan
