Opening and Closing Quantity query in postgresql

433 views postgresql
0

I'm trying to set opening and closing column in postgresql query where i input specific date range and my first row's opening column should be upto starting date and closing should be (opening column + column1 + column2 + column 3) in each date wise row.

*** Here is my sample database

Date         column1  column2 column3
01/01/2017   10       10      20
02/01/2017   10       10      20
03/01/2017   10       10      20
04/01/2017   10       10      20
05/01/2017   10       10      20
06/01/2017   10       10      20

* My expected query in postgresql * date range is 03/01/2017 to 06/01/2017

Date         opening  column1  column2  column3 closing
03/01/2017   60       10       20       10      100
04/01/2017   100      10       20       10      140     
05/01/2017   140      10       20       10      180
06/01/2017   180      10       20       10      220

answered question

sorry 03/01/2017 opening should be 80 instead of 60

1 Answer

9

You could use windowed SUM:

SELECT *, s.opening, SUM(s.opening) OVER(ORDER BY date) AS closing
FROM tab, LATERAL(SELECT col1 + col2 + col3 AS opening)s

posted this

Have an answer?

JD

Please login first before posting an answer.