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
This question is related to
mysql
aggregate-functions
having-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 > 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.
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 |
+----+----------+
When using aggregate functions to filter, you must use a HAVING statement.
SELECT *
FROM tblMoney
HAVING Sum(CASH) > 500
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.
Source: Stackoverflow.com