Question 89503 in PostgreSQL Question posted in
The official documentation can be found here.

How to get running sum within condition by postgres – Postgresql

I have following table

no status amount 
1  2      10000 
2  3      10000
3  2       1000
4  2     -11000

I got running some by using following query.

SELECT
    main.no
    , main.status
    , main.amount
    , SUM(main.amount) OVER ( 
        ORDER BY
            main.no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) 
from
    table AS main

It returned follows. But I would like to improve my formula by refering to status code.

no status amount running_sum
1  2      10000  10000
2  3      10000  20000
3  2       1000  21000
4  2     -11000  10000

My desired result is following.

I would like to get running sum only in status = 2 after another status code.

no status amount running_sum
1  2      10000  10000
2  3      10000  20000
3  2       1000  11000
4  2     -11000  0

How to add conditioning in my formula ?

Are there any good way to achieve this?

Thanks

Tags:

2

Answers


  1. 0

    You can do it using sum(amount) over(partition by status order by no)

    SELECT
    main.no
    , main.status
    , main.amount
    , SUM(main.amount) OVER ( 
        partition by status
        ORDER BY
            main.no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as running_sum
from
    mytable AS main
order by no

    Demo here

    Login or Signup to reply.
  2. 0

    According to your expected output, when status = 2, you just need to subtract the running sum of the amount where status <> 2 from your running sum, try the following:

    select main.no,
       main.status,
       main.amount,
       sum(main.amount) over (order by main.no) - 
       case when main.status = 2 
         then sum(case when main.status <> 2 then main.amount else 0 end) over (order by main.no) 
         else 0 
       end as running_sum
from table_name as main

    Demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search