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

Postgresql – How to calculate the date when the sum of entries exceeded the threshold?

Let’s assume there is a table:

day amount
1 10
2 20
3 30

I want to know when turnover reached 30, in case above it’s happened on 2 day. How to query this in SQL? I guess I have to use window function but dont’ know which one exactly

Tags:

2

Answers


  1. 0

    You could get running Total and then put filter and limit the record with LIMIT 1 or you could use MIN for the day:

    WITH cumulative_sum_table AS (
  SELECT day, amount, SUM(amount) OVER (ORDER BY day) AS running_total
  FROM your_table
)
SELECT day
FROM cumulative_sum_table
WHERE running_total >= 30
ORDER BY day
LIMIT 1;
    Login or Signup to reply.
  2. 0

    You can achieve it via aggregation

    select yt.day
from yourtable yt
join yourtable y2
on yt2.day <= yt
group by day
having SUM(yt2.amount) >= 30
limit 0, 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search