Running total from another field

Question

I have a table just like that:

date      sales    rest 
1/1/2017   52        0
2/1/2017   30        0
3/1/2017   10        200.

I only have the rest in the last date and i want to make it like this:

1/1/2017   52        240
2/1/2017   30        210
3/1/2017   10        200.

What I want to achieve to to calculate in a new column the rest for every date from the sum of the day's sales and the last days rest.

Can someone suggest a query ?

Answer 1

For SQL-Server & Postgres:

WITH RN AS
(
    SELECT date, sales, rest,
           COALESCE(LAG(sales) OVER (ORDER BY date DESC), 0) LG

    FROM sales
)
SELECT   date, sales, 
         SUM(LG + rest) OVER (ORDER BY date DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) rest
FROM     RN
ORDER BY date

date       | sales | rest
:--------- | ----: | ---:
2017-01-01 |    52 |  240
2017-01-02 |    30 |  210
2017-01-03 |    10 |  200

For MySql:

SELECT date, sales, rest
FROM (
      SELECT t.date,
             t.sales,
             @acm := @acm + t.rest + @last_sales AS rest,
             @last_sales := t.sales
      FROM   sales t,
             (SELECT @last_sales := 0, @acm := 0) r
      ORDER BY t.date desc
      ) X
ORDER BY date;

date       | sales | rest
:--------- | ----: | ---:
2017-01-01 |    52 |  240
2017-01-02 |    30 |  210
2017-01-03 |    10 |  200

SQL-Server dbfiddle here

MySQL dbfiddle here

Postgres dbfiddle here