How to calculate running SUM by SQLite query?

Question

How to get a column that is the sum of all the values ​​in front of another column?

Answer 1

You can do this by joining the table with yourself (by doing the 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 the total amount of GDP.

 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 ... 

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

Answer 2

Cross-connect solutions such as Diomidis Spinellis suggested taking O (N ^ 2) time. Recursive CTE can run faster if you can handle confusing code.

This gives the same result 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 comparison allow for duplicates, COALESCE here to ignore NULL.

If you have a good index, it should be O (N log N). Since SQLite does not support cursors, the O (N) solution probably does not exist without relying on an external application.

Answer 3

Starting with SQLite 3.25.0, starting from 2018-09-15, window functions and their keyword OVER supported. The answer to your question is now simple:

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

This is a minimal request that does what you request, but it does not determine the order, so there is a more correct way to do this.

 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; 

Answer 4

You need to make the amount in the field you need. The request depends on the database you are using. Oracle allows you to do this:

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