mysqlmysql-variables

How to fetch rows from which sum of a single integer/float column sums upto a certain value


I have a table. It has the following structure

goods_receiving_items

I am trying to fetch rows against which have the following conditions

So for example I have the following data

+----+---------+----------+------------+
| id | item_id | quantity | created_at |
+----+---------+----------+------------+
|  1 |       2 |       11 | 2019-10-10 |
|  2 |       3 |      110 | 2019-10-11 |
|  3 |       2 |       20 | 2019-11-09 |
|  4 |       2 |        5 | 2019-11-10 |
|  5 |       2 |        1 | 2019-11-11 |
+----+---------+----------+------------+

I have tried the following query:

SET @sum:= 0;
SELECT   item_id, created_at, (@sum:= @sum + quantity) AS SUM, quantity 
FROM     goods_receiving_items
WHERE    item_id = 2 AND @sum<= 6
ORDER BY created_at DESC

If I don't use ORDER BY, then the query will give me ID '1'. But if I use ORDER BY it will return all the rows with item_id = 2.

What should be returned are IDs '5' and '4' exclusively in this order

I can't seem to resolve this and ORDER BY is essential to my task. Any help would be appreciated


Solution

  • After searching around, I have made up the following query

    SELECT 
    t.id, t.quantity, t.created_at, t.sum
          FROM 
           ( SELECT 
              *,
              @bal := @bal + quantity AS sum,
              IF(@bal >= $search_number, @doneHere := @doneHere + 1 , @doneHere) AS whereToStop
                FROM goods_receiving_items
                CROSS JOIN (SELECT @bal := 0.0 , @doneHere := 0) var
                WHERE item_id = $item_id
                ORDER BY created_at DESC) AS t
                        WHERE t.whereToStop <= 1
                        ORDER BY t.created_at ASC
    

    In the above query, $search_number is a variable that holds the value that has to be reached. $item_id is the item we are searching against.

    This will return all rows for which the sum of the column quantity makes up the required sum. The sum will be made with rows in descending order by created_at and then will be rearranged in ascending order.

    I was using this query to calculate the cost when a certain amount of items are being used in an inventory management system; so this might help someone else do the same. I took most of the query from another question here on StackOverflow