How to Query for Rows Where the Sum of Previous Values Exceeds a Threshold in MySQL?

Querying with SUM() in MySQL WHERE Clause

When working with MySQL tables, it may be necessary to retrieve rows based on aggregate calculations, such as the sum of previous values. However, using SUM() directly in a WHERE clause may not yield the expected results.

Suppose you have a table with columns "id" and "cash":

id | cash
1    200
2    301
3    101
4    700

To retrieve the first row where the sum of all previous cash exceeds a specific value (e.g., 500), using WHERE SUM(cash) > 500 will not work as expected.

Solution Using HAVING Clause

MySQL allows aggregate comparisons in the HAVING clause rather than the WHERE clause. To achieve the desired result, you can use the following query:

GROUP BY ...
HAVING SUM(cash) > 500

However, this requires defining a GROUP BY clause, which may not be necessary.

Using a Nested Subquery with Running Total

An alternative approach is to use a nested subquery to calculate the running total and then compare that to the specified value in the WHERE clause:

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

In this query, a subquery is used to calculate the running total for each row in the table. The alias running_total is used to reference this value in the WHERE clause. The LIMIT clause is added to return only the first row that matches the condition.

This approach allows for the direct comparison of the aggregate value in the WHERE clause and yields the desired result without requiring a GROUP BY clause.

The above is the detailed content of How to Query for Rows Where the Sum of Previous Values Exceeds a Threshold in MySQL?. For more information, please follow other related articles on the PHP Chinese website!