SQLite: accumulator (sum) column in a SELECT statement

Question

I have a table like this one:

SELECT value FROM table;

value
1
3
13
1
5

I would like to add an accumulator column, so that I have this result:

value  accumulated
1      1
3      4
13     17
1      18
5      23

How can I do this? What's the real name of what I want to do? Thanks

Turro

Answer 1

try this way:

select value,
(select sum(t2.value) from table t2 where t2.id <= t1.id ) as accumulated
from table t1

but if it will not work on your database, just add order by something

select value,
(select sum(t2.value) from table t2 where t2.id <= t1.id order by id ) as accumulated
from table t1
order by id

this works on an oracle ;) but it should on a sqlite too

Beowulf

Had it worked on a table without id for ordering (or ordering after another criterion, without possibility of strict < or unique <= comparison), I would have accepted this answer... – Turro

You can do tthis with an analytic query when you use Oracle, no self joins needed, see orafaq.com/node/55. Sadly sqlte doesn't support analytical queries. – Roseanneroseate

Answer 2

Here's a method to create a running total without the inefficiency of summing all prior rows. (I know this question is 6 years old but it's one of the first google entries for sqlite running total.)

create table t1 (value integer, accumulated integer, id integer primary key);
insert into t1 (value) values (1);
insert into t1 (value) values (3);
insert into t1 (value) values (13);
insert into t1 (value) values (1);
insert into t1 (value) values (5);

UPDATE
    t1
SET
    accumulated = ifnull(
    (
        SELECT
            ifnull(accumulated,0)
        FROM
            t1 ROWPRIOR
        WHERE
            ROWPRIOR.id = (t1.id -1 )),0) + value;


.headers on
select * from t1;
value|accumulated|id
1|1|1
3|4|2
13|17|3
1|18|4
5|23|5

This should only be run once after importing all the values. Or, set the accumulated column to all nulls before running again.

Nalchik

This works great. I took a large table that was resisting other accumulation patterns and selected (with order by) the relevant fields in to a temp table with an autoincrement, and then used this pattern by adding a t1.itemId = ROWPRIOR.itemId to the where clause. Each time it hits a new item, the accumulation starts over. Cut a multi-hour run time to maybe 15s. – Laing

Answer 3

The operation is called a running sum. SQLite does not support it as is, but there are ways to make it work. One is just as Sebastian Brózda posted. Another I detailed here in another question.

Aleishaalejandra