0

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 ?

  • 2
    Please tag your RDBMS – McNets Jan 18 '18 at 14:33
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.