Search code examples
sqlpostgresqlsum

How to get the date when accumulation (sum) in a condition in postgresql

I have table like this:

id date total
123 2021-02-03 200000
123 2021-03-03 1650000
123 2021-02-04 1500000
123 2021-02-21 200000
123 2021-03-03 200000

How's the sql query if I want to get the date when sum of total reach 3000000 or when the date on 2021-02-04?

the expected output is

id date total
123 2021-02-04 3350000

or if not possible maybe like this:

id date
123 2021-02-04
Solution

  • use this

    select top 1 * from (
select id,date,
SUM(total) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) as Total 
from tablename
)
where total >= 3000000