Opening and Closing Quantity query in postgresql

user_9068310 0
Viewed 102

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
postgresql sql
2 Answers

You could use windowed SUM:

SELECT "date",col1,col2,col3, closing-col1-col2-col3 AS opening, closing
FROM (SELECT *, SUM(col1+col2+col3) OVER(ORDER BY "date") AS closing
      FROM tab) sub

db<>fiddle demo

A bit more concise version:

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

db<>fiddle demo2

user_50708790

This is a nice simple example for what window functions are made for (https://www.postgresql.org/docs/current/static/tutorial-window.html):

demo: db<>fiddle

SELECT
    "date",
    closing - day_value as opening, 
    column1,
    column2,
    column3,
    closing
FROM (
    SELECT 
         *, 
         column1 + column2 + column3 as day_value,
         SUM(column1 + column2 + column3) OVER (ORDER BY "date") AS closing
    FROM testdata
) s

The window function SUM adds all values of the rows before and including the current row if it is ordered (if not it sums all rows).

You need to do the window function with the whole data set before you can filter the dates

SELECT * FROM (
    -- <QUERY ABOVE>
) s
WHERE "date" BETWEEN '2017-01-03' AND '2017-01-06'
user_39842210
Related