MySQL select records that sums

Question

Hello. I am using MySQL and PHP. I have a table that contains the columns id and quantity. I would like to retrieve the id of the row that is the last to sum quantity as it reaches the number 40 at sum. To be more specific. I have 3 rows in the database. One with quantity 10, one with quantity 30 and one with quantity 20. So if I sum the quantities to have the result 40, I would sum up the first two witch means: 10 + 30 = 40. That way, the last Id that is used to sum the number 40 is 2. I just want to know the id of the last row that is used to complete the sum of 40.

I would give further details if asked. THANK YOU!!

Answer 1

Have a third column with a running total. Then you can simply return the last row where the running total <= your target value.

So your table should look like:

ID    Quantity    RunningTotal
1           10              10
2           30              40
3           20              60

NOTE: If you delete a row in the table, remember to update all subsequent rows RunningTotal -= DeletedRow.Quantity!

Answer 2

Relying on table ID's is probably a bad idea for this, but if that is how it is really done, you could try something like this (not tested):

SELECT yourTableA.id
  FROM yourTable AS yourTableA
  JOIN yourTable AS yourTableB
 WHERE ( yourTableA.value + yourTableB.value ) = 40
   AND yourTableA.id != yourTableB.id
 ORDER BY yourTableA.id

This type of solution will only work if your expecting that you only need two rows ever to equal your target sum. Since this is most likely not the case, your best bet is probably to try and get all of the rows and do this programaticly on the returned data.

The Running Total solution posted by lc is also a good option although I generally try to avoid storing calculated data unless I absolutely have to.

---

Based on the updated information from this request, I have an alternate answer.

It doesn't sound so much like you care about the inventory. You care more about when the products came in.

SELECT *
  FROM product
 ORDER BY product.receivedData

Process each record as they come in, store the price for that record, and keep going for as long as you need to until you reach the number of items you need. You should end up with a list of items, the number of inventory at that level and the price at that level.

Answer 3

Let me put it this way: I really have 6 products in my hand. The first one came to my possession on the date of 10, the next 3 came on the date of 11 and the last 2 came on 12.

Now, I want to sell 3 products from my stock. And want to sell them in the order that they came. So for the customer that wants 3 products I would sell him the product that came on 10 and 2 products from the ones that came on 11.

For the next customer that wants 2 products, I would sell him one product from the date of 11 that remains from the last order of 3 products, and another one from the ones on 12.

The question is how would I know witch price had each product I sold ? I thought that if I can find out witch rows sums up every requested quantity, I would know where to start the sum every time I want to deliver an order. So first I would look witch rows sums up 3 products and keep the entry id. For the next order I would start the count from that ID and sum until it sums up the second order of 2 products and so on. I thought that this way, I can keep track of the incoming prices that each product had. So I won't sell the products from the date of 12 at a price made up using the first prices.

I hope you understand. I just need to know what price had any of my products so I would know that the first products would have one price but as the product prices raises, I must raise my prices too...So the last products that came must be sold for a higher price. I can only achieve that if I keep track of this...

Thank you very much.

Answer 4

Nobody ? Or, even easier: MySQL should select the needed rows for SUM(quantity) to be higher or equal with 40 for example. And then to get me the id of the last row that participated at the sum process.

Answer 5

I don't understand your question too well. Can you try rewording it more properly? From what I interpret, here's the structure of your database:

ProductID    ArrivalDate
   1          10
   2          11
   3          11
   4          11
   5          12
   6          12

Now you are asking, "how would I know which price had each product I sold"? Which sorta confuses me, since each value in the database has no price attribute. Shouldn't your database look like this:

ProductID    ArrivalDate    Price
   1          10              100
   2          11              200
   3          11              300
   4          11              300
   5          12              400
   6          12              400

Personally, I think your idea to find out price sold is flawed. It would make more sense to add a few more fields to your database:

ProductID    ArrivalDate    Price     InStock   DateSold
   1          10              100       Yes        17
   2          11              200       Yes        17
   3          11              300       Yes        18
   4          11              300       Yes        18
   5          12              400       no        
   6          12              400       no

In changing your database, you can easily keep track of when a product arrives, the date sold, its price, maybe quantity (I can't tell if its an actual field or not).

Furthermore, you can simplify and make your life easier by separating the sql queries, or even adding some code to do some of the work for you.

Answer 6

I've reviewed my database. I had an idea based on your response. It's cool, this will do.

Thank you all for help!