Select from MySQL records that sums

Question

Hello. I know there are a few post out here about selecting records until a certain number is met from the sum of one field but there isn't any that suits me. I'm using PHP and MySQL.

I have a table named quantity. Inside there are records of product name, product price and product quantity. Besides these, there are a few others that helps me select the last records based on date and position, aswell as a GROUP BY the field named price because there are different quantities with different prices for the same product. So, I currently select my product specific price and quantity like this:

SELECT `price`,`quantity` FROM (SELECT `price`,`quantity` FROM `quantity` WHERE `product_name` = 'DELL' ORDER BY `date` DESC, `position`) AS `Actions` GROUP BY `price`

This query is a workaround because I need to get data like this:

product_name   |    price  |    quantity

DELL           |     100   |      30
DELL           |     120   |      10
DELL           |     130   |      2

asuming that I have multiple records like these and I need to get the latest of them. Anyway, from this query I need to do the following: I need to select the records who's quantity summed with another product's quantity equals 35. So, by using my query I know that it should stop at line 2 because I can take the 30 products that came with the price of $100 and another 5 products from the line 2 that has price of 120. And then I would need to enter my updates. So, the new data would look like:

product_name   |    price  |    quantity

DELL           |     100   |      0
DELL           |     120   |      5
DELL           |     130   |      2

How in the world am I going to achieve this ? I hope my details are clear. Please, ask any question.

Thank you for your time!

Answer 1

Option 1: Use program logic instead of a query:

There is nothing wrong with using the programming layer to do more advanced database interactions. SQL is not an answer to everything... (Also consider a stored procedure).

enough = 35
running_total = 0

START TRANSACTION
while running_total < enough:
    select one record order by price limit 1 FOR UPDATE
    add to running_total

UPDATE records...
COMMIT

---

Option 2: Use a query with a running total:

In this option, you obtain a running total using a derived query, and then filter that down to specific records in the outer query. If you intend on updating them, you should wrap this in a transaction with the right isolation level.

SET @running_total = 0;
SELECT
    row_id,
    product_name,
    price,
    quantity
FROM
    (
    SELECT 
        row_id, 
        product_name, 
        price, 
        quantity, 
        @running_total := @running_total + quantity AS running_total
    FROM 
        sometable
    WHERE
        quantity > 0
    ORDER BY
        quantity
    LIMIT 
        35  /* for performance reasons :) */
    ) as T1
WHERE
    running_total < 35

---

I would tend to prefer option 1 because it is more "obvious", but perhaps this will give you some food for thought.