aggregate functions - use mysql SUM() in a WHERE clause

Question

suppose I have this table

id | cash 
1    200
2    301
3    101
4    700

and I want to return the first row in which the sum of all the previous cash is greater than a certain value:

So for instance, if I want to return the first row in which the sum of all the previous cash is greater than 500, is should return to row 3

How do I do this using mysql statement?

using WHERE SUM(cash) > 500 doesn't work

Answer 1

You can only use aggregates for comparison in the HAVING clause:

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

The HAVING clause requires you to define a GROUP BY clause.

To get the first row where the sum of all the previous cash is greater than a certain value, use:

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

Because the aggregate function occurs in a subquery, the column alias for it can be referenced in the WHERE clause.