Using MySQL SUM() Function in a WHERE Clause
In MySQL, using the SUM() function in a WHERE clause directly is limited. Instead, you can use the HAVING clause in conjunction with the SUM() function.
WHERE SUM(cash) > 500 Issue:
The provided query using WHERE SUM(cash) > 500 will not work because aggregate functions like SUM() cannot be used for comparison in a WHERE clause.
HAVING Clause:
To use aggregate functions for comparison, you need to use the HAVING clause. The HAVING clause is used with the GROUP BY clause to constrain the grouping results.
Example Query:
To find the first row where the running total of cash exceeds 500:
<code class="sql">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;</code>
Explanation:
The above is the detailed content of How Can I Use the SUM() Function for Comparisons in a MySQL WHERE Clause?. For more information, please follow other related articles on the PHP Chinese website!