OPessoa Blog

Generating month's sums from a table with date and value

Posted on 2016-11-14 19:13:35


First of all, how can I generate a table with all months even with zero values in sqlite?
Using only group from dates I think that's is impossible, or better, it's possible only if have values in all months.
We can use the clause WITH.

WITH month_table as (

SELECT '01' as month,'Jan' as desc UNION ALL

SELECT '02','Feb' UNION ALL

SELECT '03','Mar' UNION ALL

SELECT '04','Apr' UNION ALL

SELECT '05','May' UNION ALL

SELECT '06','Jun' UNION ALL

SELECT '07','Jul' UNION ALL

SELECT '08','Aug' UNION ALL

SELECT '09','Sep' UNION ALL

SELECT '10','Oct' UNION ALL

SELECT '11','Nov' UNION ALL

SELECT '12','Dec')


SELECT sum(value) FROM month_table t left join finance on user_id = 4 and strftime('%Y', dt_lancamento) = '2015' and value > 0 and strftime('%m', dt_lancamento) = t.month group by t.month;

Now, lets think about how to do the same for the days of month!?