limiting the rows to where the sum a column equals a certain value in MySQL

mysql, sql

Solution

Here's a way which should work in MySQL :

SELECT
  O.Id,
  O.Type,
  O.MyAmountCol,
  (SELECT
     sum(MyAmountCol) FROM Table1
   WHERE Id <= O.Id) 'RunningTotal'
FROM Table1 O
HAVING RunningTotal <= 7

It involves calculating a running total and selecting records while the running total is less than or equal to the given number, in this case `7`.

SQL Fiddle

Problem

I want to write a query which returns all rows until the sum of one of the columns value reaches a certain value. For example in the table below: ``` DATE ETC Meeting 2013-02-01 00:00:00 85482 1 2013-02-01 00:00:00 47228 2 2013-02-02 00:00:00 12026 4 2013-02-03 00:00:00 78927 6 2013-02-04 00:00:00 85662 2 2013-03-05 00:00:00 47978 1 2013-08-07 00:00:00 8582 1 ``` If I want to get the rows until the sum of column `Meeting` equals 7. ``` DATE ETC Meeting 2013-02-01 00:00:00 85482 1 2013-02-01 00:00:00 47228 2 2013-02-02 00:00:00 12026 4 ``` If I want to get the rows until the sum of column `Meeting` equals 13. ``` DATE ETC Meeting 2013-02-01 00:00:00 85482 1 2013-02-01 00:00:00 47228 2 2013-02-02 00:00:00 12026 4 2013-02-03 00:00:00 78927 6 ```

Original source