CODEWITHSUNDEEP
sqlitecumulative-sum
Arnes
Arnes

Reputation: 402

Cumulative SUM in SQLite

I wrote a query in SQLite that cumulates VALUE and it is good if I order by ID only (ID is auto increment int). But, if I want to order by DATE and then by ID, the query shows me wrong results. Any ideas?

Query:

select t1.ID, 
       t1.DATE, 
       t1.VALUE, 
       sum(t2.VALUE) as TOTAL     
from test t1
inner join test t2 on t1.DATE >= t2.DATE and t1.id >= t2.id 
group by t1.ID, t1.DATE
order by t1.DATE desc, t1.ID desc

Results:

ID  DATE        VALUE   TOTAL
9   2015-01-16  55      281 -- should be 226 + 55 = 281
6   2015-01-15  10      26  -- should be 216 + 10 = 226
5   2015-01-15  5       16  -- should be 211 +  5 = 216
8   2015-01-14  100     211
7   2015-01-14  100     111
4   2015-01-13  5       11
3   2015-01-12  3       6
2   2015-01-11  2       3
1   2015-01-10  1       1

Upvotes: 1

Views: 2630

Answers (1)

Captain
Captain

Reputation: 2218

At least on the small data, changing the join to something like this:

t1.DATE||substr('00000'||t1.id,-5) >= t2.DATE||substr('00000'||t2.id,-5)

works fine... So we are building up a "sort key" based on the date and the id (padded with zeros to 5 digits) and using that in the join. It might be necessary to build an index on that to deal with performance though

Upvotes: 1

Related Questions