Calculate running difference between two columns in Postgresql

Question

I have data in two tables as below

Table 1 :

Material |Ordr Qty 
---------|---------
abcd     |4253

Table 2 :

Material | Stck Qty 
---------|---------
abcd     |1000
abcd     |2000
abcd     |2000

Expected Output :

Material |Ordr Qty |Stck Qty |Column D
---------|---------|---------|---------
abcd     |4253     |1000     |3253
abcd     |4253     |2000     |1253
abcd     |4253     |2000     |-747

Logic for column D is like

4253-1000 = 3253
3253-2000 = 1253
1253-2000 = -747

LAG(ordr qty - stck qty,1,0)over (ORDER BY material)-stck qty

I am trying above LAG function and getting below output

abcd    4253    1000    -1000
abcd    4253    2000    1253
abcd    4253    2000    253

Let me know how to achieve the expected output.

GMB · Accepted Answer · 2020-09-24 19:05:41Z

1

Consider a window sum() rather than lag():

select t1.*, t2.stck_qty,
    t1.ordr_qty - sum(t2.stck_qty) over(partition by material order by t2.id)
from t1
inner join t2 using(material)
order by material, t2.id

To get a stable result, you need a column that defines the ordering of the rows in t2: I assumed id.

answered Sep 24, 2020 at 19:05

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Calculate running difference between two columns in Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related