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

Question

I have a table. It has the following structure

goods_receiving_items

• id
• item_id
• quantity
• created_at

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

• Has one item_id

• When the sum of the quantity column equals a certain value

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

Answer 1

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

Answer 2

You should use the order by on the resulting set you could do this using a subquery

  SET @sum:= 0;

  select t.* 
  from t (
    SELECT item_id
        , created_at
        , (@sum:= @sum + quantity) as sum
        , quantity 
    FROM goods_receiving_items
    WHERE item_id = 2 AND @sum<= 6
  ) t
  ORDER BY created_at DESC

Answer 3

You should try an INNER JOIN with SELECT min(created_at) or SELECT max(created_at)

From MYSQL docs:

...the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses.

The answers on the following might help in more detail: MYSQL GROUP BY and ORDER BY not working together as expected