I am trying to write a query in PHP that searches the database (By date, Desc) getting the Sum of the Cost Column until the cumulative sum of the Quantity column reaches a certain number.
Something like this:
SELECT Sum(Cost), Sum(Quantity) FROM Table UNTIL cumulative_total of (Quantity) <= 1000 WHERE Product='myProduct' ORDER BY `Date` Desc;
I thought this might work, but it returns no results:
SELECT Quantity, Cost, (@csum := @csum Quantity) as cumulative_sum FROM Orders WHERE Product='selectedProduct' HAVING @csum <= '1500' ORDER BY Date DESC;
The purpose is that I want to be able to get the Average cost (Cost/Quantity) of recent entries in the database, but the Quantity will be a user-generated variable.
UPDATE: Here is an example of the table structure:
| ID | Product | Quantity | Cost | Date |
|---|---|---|---|---|
| 1 | Fork | 10 | 5.00 | 2022-04-19 |
| 2 | Fork | 10 | 5.00 | 2022-04-19 |
| 3 | Knife | 7 | 5.00 | 2022-04-20 |
| 4 | Fork | 20 | 10.00 | 2022-04-20 |
| 5 | Fork | 10 | 5.00 | 2022-04-21 |
If I wanted to know the average cost of the last 30 Forks, How would I proceed?
CodePudding user response：
It would be better if you could include a sample data in an online database fiddle. However please do try this
SELECT *, @running_sum:=@running_sum Quantity AS cumulativeSum FROM ( SELECT * FROM Orders o WHERE o.Product = 'selectedProduct' )a, (SELECT @running_sum:=0)rn ORDER BY DATE DESC;