use mysql SUM() in a WHERE clause

mysql aggregate-functions having-clause
kamikaze_pilot picture kamikaze_pilot · Jul 19, 2010 · Viewed 131.6k times · Source

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

OMG Ponies picture OMG Ponies · Jul 19, 2010

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.

Related questions

SQL select only rows with max value on a column

I have this table for documents (simplified version here): +------+-------+--------------------------------------+ | id | rev | content | +------+-------+--------------------------------------+ | 1 | 1 | ... | | 2 | 1 | ... | | 1 | 2 | ... | | 1 | 3 | ... | +------+-------+--------------------------------------+ How do I select one row per id and only the greatest rev? With the above data, the result …

Read More
SELECTING with multiple WHERE conditions on same column

Ok, I think I might be overlooking something obvious/simple here... but I need to write a query that returns only records that match multiple criteria on the same column... My table is a very simple linking setup for applying …

Read More
How to SUM and SUBTRACT using SQL?

I am using MySQL and I have two tables: master_table ORDERNO ITEM QTY stock_bal ITEM BAL_QTY Master table has duplicate ORDERNO and ITEM values. I have get total QTY using SQL 'GROUP BY' clause. I need to …

Read More