CODEWITHSUNDEEP
sqlpostgresql
java
java

Reputation: 1214

How to calculate a cumulative sum from the bottom up?

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?

Upvotes: 2

Views: 584

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93754

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

Upvotes: 1

200_success
200_success

Reputation: 7591

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.

Upvotes: 0

Related Questions