How do I calculate a running SUM on a SQLite query?

sql sqlite aggregate-functions
Hugo picture Hugo · Apr 9, 2011 · Viewed 11.8k times · Source

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

Answer

relatively_random picture relatively_random · Oct 11, 2019

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.

Related questions

How to load extensions into SQLite?

I need a standard deviation function in SQLite. I have found one here: http://www.sqlite.org/contrib?orderby=date but its part of an extension file to SQLite. I've never installed one of these before and I don't know …

Read More
SQL select elements where sum of field is less than N

Given that I've got a table with the following, very simple content: # select * from messages; id | verbosity ----+----------- 1 | 20 2 | 20 3 | 20 4 | 30 5 | 100 (5 rows) I would like to select N messages, which sum of verbosity is lower than Y (for testing purposes let's say …

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More