I have a query in PostgreSQL which yields:
itemorder name qty 1 A -20 2 A2 350 3 A 50 4 A -10 5 A2 10 the itemorder column gives the correct order of rows as I expect to see. I need to pass over the rows from bottom to top and calculate a new column that has an initial value of 100 and does + qty for each row of A.
itemorder name qty modifyed_sum 1 A -20 120 / 140 + (-20) 2 A2 350 140 / not A 3 A 50 140 / 90 + 50 4 A -10 90 / 100 + (-10) 5 A2 10 100 / not A How can I do that?
Try this
SELECT 100+ Sum(CASE WHEN name = 'a' THEN qty ELSE 0 END)OVER(ORDER BY itemorder DESC) as modifyed_sum, qty, name, itemorder FROM Yourtable ORDER BY itemorder ASC Another way
SELECT 100 + (SELECT Sum(CASE WHEN b.name = 'a' THEN b.qty ELSE 0 END) FROM yourtable b WHERE a.itemorder <= b.itemorder), qty, name, itemorder FROM yourtable a ORDER BY itemorder ASC 
SELECT itemorder , name , qty , 100 + SUM(CASE WHEN name = 'A' THEN qty ELSE 0 END) OVER (ORDER BY itemorder ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS modifyed_sum FROM thetable ORDER BY itemorder; The ROWS BETWEEN … feature is a value expression to be used in a window function.