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   gt  500 doesn t work

User · Accepted Answer

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.

User · Answer

When using aggregate functions to filter   you must use a HAVING statement   SELECT   FROM tblMoney HAVING Sum CASH   gt  500

User · Answer

Not tested  but I think this will be close   SELECT m1 id FROM mytable m1 INNER JOIN mytable m2 ON m1 id  lt  m2 id GROUP BY m1 id HAVING SUM m1 cash   gt  500 ORDER BY m1 id LIMIT 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

User · Answer

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  gt  prev id    GROUP BY t id  AS s WHERE s cash sum  gt   500 ORDER BY s id LIMIT 1    Output    ---- ----------    id   cash sum    ---- ----------     3        501    ---- ----------

[mysql] use mysql SUM() in a WHERE clause

Examples related to mysql

Examples related to aggregate-functions

Examples related to having-clause