Sum until certain point - MySql

Question

How to do query and display the records until it reaches a certain number?

Suppose you want to select student until the total sum of the student's money reaches 1000?

Addition

 Student ID   Student Name   Student Money 
 ---------    -----------     --------------
   1           John            190
   2           Jenny           290
   3           Ben             200
   4           Andy            120
   5           Lynna           300

If I wanna stop at 500, I would get record number 1 and 2 (190 + 290). If I wanna stop at 1000, I would get record 1 until 4.

Answer 1

Came across this question while searching for my own answer. I thought I'd leave my solution here since it's another way to accomplish the same task and may be more efficient. The trick is the self join using >=

    SELECT s1.ID, s1.name, s1.money, sum(s2.money) 
    FROM student s1 
    INNER JOIN student s2 ON s1.id >= s2.id 
    GROUP BY s1.id HAVING SUM(s2.money) <= 500;

Answer 2

There is no "intrinsic" order to a SQL table, so you'll have to specify some ORDER BY clause to give that "until" phrase any meaning. Given that, the sum of the ``first'' N records can be obtained with a SELECT SUM(money) FROM student ORDER BY xxx LIMIT N. Using an auxiliary table INTS which has integers in natural order, you can find the maximum suitable N by something like:

SELECT MAX(N) FROM INTS
WHERE (SELECT SUM(money) FROM student ORDER BY xxx LIMIT N) < 1000

and finally insert this as another nested SELECT for the LIMIT clause in your overall SELECT. All of this smells like it would be rather inefficient, though! As often when nested SELECTs seem too many and too slow, an alternative is doing this in steps: first build a temporary table with the "progressive sums", then use that to help you find the limit you need.

Answer 3

Oops ... MySQL ... this solution is for MS SQL ...

Here is a solution using the ROW_NUMBER() function.

SELECT Student.*, SUM(StudentBefore.Money) AS AccumulatedMoney  
FROM (  
       SELECT *, ROW_NUMBER() OVER(ORDER BY Id) AS RowNumber  
       FROM Students  
     ) AS Student  
     INNER JOIN  
     (  
       SELECT *, ROW_NUMBER() OVER(ORDER BY Id) AS RowNumber  
       FROM Students  
     ) AS StudentBefore  
     ON StudentBefore.RowNumber <= Student.RowNumber  
GROUP BY Student.RowNumber, Student.Id, Student.Name, Student.Money  
HAVING SUM(StudentBefore.Money) < 1000  

The execution plan indicates that sorting the table is the most expensive operation. If there is an index on the columns to sort by - you example indicates you want to sort by the primary key id - the index scan will become the most expensive operation.