# use mysql SUM() in a WHERE clause

MysqlAggregate FunctionsHaving Clause## Mysql Problem Overview

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

## Mysql Solutions

## Solution 1 - Mysql

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.

## Solution 2 - Mysql

Not tested, but I think this will be close?

```
SELECT m1.id
FROM mytable m1
INNER JOIN mytable m2 ON m1.id < m2.id
GROUP BY m1.id
HAVING SUM(m1.cash) > 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.

## Solution 3 - Mysql

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 s
WHERE s.cash_sum >= 500
ORDER BY s.id
LIMIT 1;
```

Output:

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

## Solution 4 - Mysql

When using aggregate functions to filter, you must use a HAVING statement.

```
SELECT *
FROM tblMoney
HAVING Sum(CASH) > 500
```