How do I calculate a running SUM on a SQLite query

Question

How do I get a column that is the sum of all the values before of another column?

Answer 1

If you have a version of SQLite that doesn't support OVER here is another approach using recursion against a group_concat string of rows.
On SQLite version 3.22.0 2018-01-22 18:45:57 group_concat returns rows in database order. Create a common table expression and order it for different orders as in table work1 in the example.

/* cumulative running total using group_concat and recursion
   adapted from https://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial/
*/

WITH RECURSIVE work2 AS (
  SELECT NULL AS name, NULL AS gdppc, 0 AS cum, (select group_concat(name) from work1) AS gcname, (select group_concat(gdppc) from work1) AS gcgdppc 
  UNION
        SELECT
            CASE
                WHEN INSTR(gcname, ',' )>0 THEN 
                    SUBSTR(gcname, 0, INSTR(gcname,','))
                ELSE
                    gcname
            END,
            CASE
                WHEN INSTR(gcgdppc, ',' )>0 THEN 
                    SUBSTR(gcgdppc, 0, INSTR(gcgdppc,','))
                ELSE
                    gcgdppc
            END,
            CASE
                WHEN INSTR(gcgdppc, ',' )>0 THEN 
                    cum + SUBSTR(gcgdppc, 0, INSTR(gcgdppc,','))
                ELSE
                    cum + gcgdppc
            END,
            CASE
                WHEN INSTR( gcname, ',' )>0 THEN 
                    SUBSTR( gcname, INSTR( gcname, ',' )+1 )
                ELSE
                    NULL
            END,
            CASE
                WHEN INSTR(gcgdppc, ',' )>0 THEN 
                    SUBSTR( gcgdppc, INSTR( gcgdppc, ',' )+1 )
                ELSE
                    NULL
            END
        FROM work2
        WHERE gcgdppc IS NOT NULL

  ), 
/* SQLite version 3.22.0 2018-01-22 18:45:57
   group_concat ignores ORDER BY when specified against the base table
   but does appear to follow the order of a common table expression 
*/
  work1 AS (select * from gdppc order by gdppc),

  gdppc AS (SELECT 'Burundi' AS name,399.657 AS gdppc
            UNION
            SELECT 'Democratic Republic of Congo', 329.645
            UNION
            SELECT 'Liberia',385.417
            UNION
            SELECT 'Zimbabwe',370.465)

select name,gdppc,cum from work2 where name IS NOT NULL;
/* result
Democratic Republic of Congo|329.645|329.645
Zimbabwe|370.465|700.11
Liberia|385.417|1085.527
Burundi|399.657|1485.184
*/

Answer 2

You can do it by joining the table with itself (performing a so-called Cartesian or cross join). See the following example.

SELECT a.name, a.gdppc, SUM(b.gdppc)
FROM gdppc AS a, gdppc AS b WHERE b.gdppc <= a.gdppc 
GROUP BY b.id ORDER BY a.gdppc;

Given a table containing countries and their per capita GDP it will give you a running total of the GDP figure.

Democratic Republic of Congo|329.645|329.645
Zimbabwe|370.465|700.11
Liberia|385.417|1085.527
Burundi|399.657|1485.184
Eritrea|678.954|2164.138
Niger|711.877|2876.015
Central African Republic|743.945|3619.96
Sierra Leone|781.594|4401.554
Togo|833.803|5235.357
Malawi|867.063|6102.42
Mozambique|932.511|7034.931
...

Note that this can be a very resource-intensive operation, because if a table has N elements it will create a temporary table with N*N elements. I would not perform it on a large table.

Answer 3

You have to do a sum in the field you want.... The query depends on the database you're using, Oracle allows you to do this:

select id, value, sum(value) as partial_sum over (order by id) from table

Answer 4

Cross join solutions like Diomidis Spinellis suggested take O(N^2) time. A recursive CTE can work faster, if you can stomach the convoluted code.

This produces the same output as his.

WITH RECURSIVE running(id, name, gdppc, rt) AS (
    SELECT row1._rowid_, row1.name, row1.gdppc, COALESCE(row1.gdppc,0)
    FROM gdppc AS row1
    WHERE row1._rowid_ = (
        SELECT a._rowid_
        FROM gdppc AS a
        ORDER BY a.gdppc, a.name, a._rowid_
        LIMIT 1)
    UNION ALL
    SELECT row_n._rowid_, row_n.name, row_n.gdppc, COALESCE(row_n.gdppc,0)+running.rt
    FROM gdppc AS row_n INNER JOIN running
    ON row_n._rowid_ = (
        SELECT a._rowid_
        FROM gdppc AS a
        WHERE (a.gdppc, a.name, a._rowid_) > (running.gdppc, running.name, running.id)
        ORDER BY a.gdppc, a.name, a._rowid_
        LIMIT 1))
SELECT running.name, running.gdppc, running.rt
FROM running;

Ordering and comparisons take care of duplicates, COALESCE is there to ignore NULLs.

If you have a good index, this should be O(N log N). Since SQLite doesn't support cursors, an O(N) solution probably doesn't exist without relying on an external application.

Answer 5

As of SQLite 3.25.0, since 2018-09-15, window functions and their keyword OVER are supported. The answer to your question is now easy:

SELECT Country, Gdp, SUM(Gdp) OVER (ROWS UNBOUNDED PRECEDING)
FROM CountryGdp;

This is the minimal query that does what you request, but it doesn't define any ordering so here is a more proper way of doing it.

SELECT
    Country,
    Gdp,
    SUM(Gdp) OVER (
        ORDER BY Country -- Window ordering (not necessarily the same as result ordering!)
        ROWS BETWEEN -- Window for the SUM includes these rows:
            UNBOUNDED PRECEDING -- all rows before current one in window ordering
            AND CURRENT ROW -- up to and including current row.
        ) AS RunningTotal
FROM CountryGdp
ORDER BY Country;

In any way, the query should run in O(N) time.