Josef Josef - 3 months ago 10
MySQL Question

Return rows where sum on a field amount is reached from MySQL query

My knowledge of MySQL is basic. I want to build a query to return all rows when is reached that sum a given amount value


  • given value: 1.20

  • field to sum: amount

  • order by expirydate



table:

id name expirydate amount
1 name1 11-08-16 0.20
2 name2 10-08-16 1.50
3 name3 08-08-16 1.00


I need the result from id 3, 2 order by expiry date.

Answer

You can accomplish this using MySQL user defined variables

SET @n := 1.2;

SELECT 
 t.id,
 t.name,
 t.expirydate,
 t.amount
FROM 
(
    SELECT 
    *,
    @bal := @bal + amount AS runningAmount,
    IF(@bal >= @n, @doneHere := @doneHere + 1 , @doneHere) AS whereToStop
    FROM table_amount
    CROSS JOIN (SELECT @bal := 0.0 , @doneHere := 0) var
    ORDER BY expirydate
) AS t
WHERE t.whereToStop <= 1

See a WORKING DEMO

Comments