use mysql SUM() in a WHERE clause

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 > 500ORDER 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.

Not tested, but I think this will be close?

SELECT m1.idFROM mytable m1INNER JOIN mytable m2 ON m1.id < m2.idGROUP BY m1.idHAVING SUM(m1.cash) > 500ORDER BY m1.idLIMIT 1,2

The idea is to SUM up all the previous rows, get only the ones where the sum of the previous rows is > 500, then skip one and return the next one.

In general, a condition in the WHERE clause of an SQL query can reference only a single row. The context of a WHERE clause is evaluated before any order has been defined by an ORDER BY clause, and there is no implicit order to an RDBMS table.

You can use a derived table to join each row to the group of rows with a lesser id value, and produce the sum of each sum group. Then test where the sum meets your criterion.

CREATE TABLE MyTable ( id INT PRIMARY KEY, cash INT );INSERT INTO MyTable (id, cash) VALUES  (1, 200), (2, 301), (3, 101), (4, 700);SELECT s.*FROM (  SELECT t.id, SUM(prev.cash) AS cash_sum  FROM MyTable t JOIN MyTable prev ON (t.id > prev.id)  GROUP BY t.id) AS sWHERE s.cash_sum >= 500ORDER BY s.idLIMIT 1;

Output:

+----+----------+| id | cash_sum |+----+----------+|  3 |      501 |+----+----------+