How to Find the First Row Where Cumulative Cash Sum Exceeds a Limit in MySQL?

Retrieving the First Row with Previous Cash Sum Exceeding a Limit Using MySQL

Problem Definition:

Given a table with columns id and cash, the task is to retrieve the first row where the cumulative sum of cash in previous rows exceeds a specified value. For instance, if the desired limit is 500, the function should return the third row where the cumulative sum of cash reaches over 500.

MySQL Solution:

While attempting to compare aggregates in the WHERE clause (e.g., WHERE SUM(cash) > 500) proves unsuccessful, the HAVING clause can be utilized to compare aggregates. However, the HAVING clause requires a corresponding GROUP BY clause.

To resolve this, the following query can be employed:

SELECT y.id, y.cash
FROM (
  SELECT t.id,
  t.cash,
  (
    SELECT SUM(x.cash)
     FROM TABLE x
    WHERE x.id <= t.id
  ) AS running_total
  FROM TABLE t
  ORDER BY t.id
) y
WHERE y.running_total > 500
ORDER BY y.id
LIMIT 1

This query utilizes a subquery to calculate the cumulative sum of cash for each row, which is stored in the running_total column alias. By referencing the running_total column in the WHERE clause, the first row where the cumulative sum exceeds the specified limit can be retrieved. The LIMIT 1 clause ensures that only the first such row is returned.

The above is the detailed content of How to Find the First Row Where Cumulative Cash Sum Exceeds a Limit in MySQL?. For more information, please follow other related articles on the PHP Chinese website!