CODEWITHSUNDEEP
mysqlsqlsum
hhh3112
hhh3112

Reputation: 2187

SQL sum of previous entries

I have a table:

year   val
----   ----
2013    4
2014    6
2014    2
2014    6
2015    1
2015    3
2016    7

is there a way to get the sum of the previous years for every year in the table? the result should be like this:

2013   0
2014   4
2015   18
2016   22
2017   29

I tried something like this:

select year, sum(val) from 
(select year, val from table ?????)
group by year

There should be an inner join somewhere maybe?

Upvotes: 0

Views: 63

Answers (3)

ogres
ogres

Reputation: 3690

If you want old years only then use this query

SELECT DISTINCT year , ( SELECT SUM(val) FROM table as temp2 WHERE temp2.year < temp1.year ) as v FROM table as temp1

If you want to include year too then change temp2.year < temp1.year to <= , if you want to filter by years , then use comparison =

so

SELECT DISTINCT year , ( SELECT SUM(val) FROM table as temp2 WHERE temp2.year <= temp1.year ) as v FROM table as temp1

and

SELECT DISTINCT year , ( SELECT SUM(val) FROM table as temp2 WHERE temp2.year = temp1.year ) as v FROM table as temp1

but the latest could be easily done without subquery , just selecting year and sum(val) then group by year

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269593

Your question is a bit challenging because you want 0 for the first year:

select year,
       ( (@p := @p + sumval) - sumval) as cumeprev
from (select year, sum(val) as sumval
      from table t
      group by year
     ) t cross join
     (select @p := 0) params
order by year;

Upvotes: 1

juergen d
juergen d

Reputation: 204746

Try

select year, @previous := @previous + sum(val)
from your_table
cross join (select @previous := 0) p
group by year
order by year

Upvotes: 0

Related Questions