﻿ Cumulating Value of Current Row + Sum of Previous Rows - ITCodar

# Cumulating Value of Current Row + Sum of Previous Rows

## SUM from previous ROW values

You can use the cumulative sum function (ANSI SQL):

``with t as (      <your query here>     )select t.*,       sum(receipt) over (order by date, shift) as totalreceipt,       sum(issue) over (order by date, shift) as totalissue,       sum(issue - receipt) over (order by date, shift) as variancefrom t;``

## Cumulatively sum a portion of a previous value with its next value

I would use a `for` loop to do this. It's important to initialize a vector first, especially if you're working with a large data set.

``# initializenewx <- vector("numeric", length(df\$x))newx <- df\$xfor(i in 2:length(df\$x)){  newx[i] <- df\$x[i] + (0.8 * newx[i-1])}newx#   1.00000  2.80000  5.24000  8.19200 11.55360 15.24288 19.19430 23.35544 27.68435 32.14748``

## SQL query to calculate sum of row with previous row

``SELECT t.date, (    SELECT SUM(numsubs)     FROM mytable t2     WHERE t2.date <= t.date) AS cntFROM mytable t``

## Sum of previous rows and multiply with value from another column

While your post is lacking some vital information...I don't necessarily blame you because it's a difficult problem to both explain and to solve.

It looks like you're basically trying to come up with a compounding interest calculator. Except in this case, the rate changes every year.

To calculate the PRODUCT aggregate of your forecasts, I found this blog post:
https://blog.jooq.org/2018/09/21/how-to-write-a-multiplication-aggregate-function-in-sql/

It just required a very tiny bit of tweaking.

``DECLARE @StartingYear int = 2021,        @StartingBudget decimal(12, 2);SELECT @StartingBudget = yt.budgetFROM #YourTable ytWHERE yt.startyear = @StartingYearSELECT yt.startyear, yt.division, yt.account, yt.budget, yt.forecast, YearBudget = yt.budget, PrevYearDiff = yt.budgetFROM #YourTable ytWHERE yt.startyear = @StartingYearUNION ALLSELECT x.startyear, x.division, x.account, x.budget, x.forecast     , YearBudget = CONVERT(decimal(10,2), x.SumProdBudget)     , PrevYearDiff = CONVERT(decimal(10,2), x.SumProdBudget - LAG(x.SumProdBudget,1,@StartingBudget) OVER (ORDER BY x.startyear))FROM (    SELECT yt.startyear, yt.division, yt.account, yt.budget, yt.forecast        , SumProdBudget = EXP(SUM(LOG(1+yt.forecast)) OVER (ORDER BY yt.startyear)) * @StartingBudget    FROM #YourTable yt    WHERE yt.startyear > @StartingYear) x``

Returns:

``| startyear | division | account | budget     | forecast    | YearBudget | PrevYearDiff | |-----------|----------|---------|------------|-------------|------------|--------------| | 2021      | 40       | 4100    | 5122952.22 | 0.012306656 | 5122952.22 | 5122952.22   | | 2022      | 40       | 4100    | 0.00       | 0.011424198 | 5181477.84 | 58525.62     | | 2023      | 40       | 4100    | 0.00       | 0.010431491 | 5235528.38 | 54050.54     | | 2024      | 40       | 4100    | 0.00       | 0.009311863 | 5284280.90 | 48752.52     | | 2025      | 40       | 4100    | 0.00       | 0.008324122 | 5328267.90 | 43987.00     | | 2026      | 40       | 4100    | 0.00       | 0.007763793 | 5369635.47 | 41367.57     | ``

The key to this whole thing is this line:

``SELECT SumProdBudget = EXP(SUM(LOG(1+yt.forecast)) OVER (ORDER BY yt.startyear)) * @StartingBudget``

This is saying to return a running product of all previous forecasts, then multiply that by the original budget. This will produce the budget for each year, technically based on the budget of the previous year.

Then once you have that, I stuck it in a sub-query to find the difference between the year and its previous year.

#### Sample Data:

``IF OBJECT_ID('tempdb..#YourTable','U') IS NOT NULL DROP TABLE #YourTable; --SELECT * FROM #YourTableCREATE TABLE #YourTable (    startyear   int             NOT NULL,    division    int             NOT NULL,    account     int             NOT NULL,    budget      decimal(12, 2)  NOT NULL,    forecast    decimal(10, 9)  NOT NULL,);INSERT INTO #YourTable (startyear, division, account, budget, forecast)VALUES (2021, 40, 4100, 5122952.22, 0.012306656)     , (2022, 40, 4100, 0         , 0.011424198)     , (2023, 40, 4100, 0         , 0.010431491)     , (2024, 40, 4100, 0         , 0.009311863)     , (2025, 40, 4100, 0         , 0.008324122)     , (2026, 40, 4100, 0         , 0.007763793)     , (2027, 40, 4100, 0         , 0.007557735)     , (2028, 40, 4100, 0         , 0.007357883)     , (2029, 40, 4100, 0         , 0.007160051)     , (2030, 40, 4100, 0         , 0.006953345)     , (2031, 40, 4100, 0         , 0.006737952)     , (2032, 40, 4100, 0         , 0.006535297)     , (2033, 40, 4100, 0         , 0.006364179)     , (2034, 40, 4100, 0         , 0.006213237)     , (2035, 40, 4100, 0         , 0.006085724)     , (2036, 40, 4100, 0         , 0.005944279)     , (2037, 40, 4100, 0         , 0.005758285)     , (2038, 40, 4100, 0         , 0.005559474)     , (2039, 40, 4100, 0         , 0.005360105)     , (2040, 40, 4100, 0         , 0.005163794)     , (2041, 40, 4100, 0         , 0.004972228);``

## Sum of values from previous row columns + value of current row

In SQL Server 2008, you can use `cross apply`:

``select t.*, tt.GSumABCfrom t cross apply     (select sum(valueA + valueB + ValueC) as GSumABC      from t t2      where t2.id <= t.id     ) tt;``

You can also phrase this as a correlated subquery:

``select t.*,       (select sum(valueA + valueB + ValueC) as GSumABC        from t t2        where t2.id <= t.id       ) as GSumABCfrom t;``