How do I get a column that is the sum of all the values before of another column?
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With