Home > Database > Mysql Tutorial > How to Query for Rows Where the Sum of Previous Values Exceeds a Threshold in MySQL?

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

Susan Sarandon
Release: 2024-11-05 01:35:01
Original
420 people have browsed it

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
Copy after login

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
Copy after login

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
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template