Sql Query to find average cost of last n rows using PHP

Question

I am trying to write a query in PHP that will retrieve the number of rows in a database (By date or ID, Descending) needed to match (or exceed) the sum of the quantity column with a given value x.

That number will give me the LIMIT number (x) I need to find the average cost of x number of rows of the cost column.

The idea is to find out the value of my on-hand inventory by going back only as far in the database as the quantity I have on hand. It effectively tells me How much I paid for the last n quantity of an item by looking back x rows.

I have tried to split it into 2 queries, First getting the limit number (inside a loop with $limitNumber increasing by 1 until the sum(quantity) >= given value n:

SELECT sum(Quantity) as theSum
      FROM Receiving
      WHERE Product='$productID'
      ORDER BY ID DESC
      LIMIT $limitNumber;

$limitNumber++;

second I would get the average after knowing how to get the limit number. Something like this:

SELECT ID, AVG((Total+Adjustment) / Quantity) as costAvg
              FROM Receiving
              WHERE Product='$productID'
              ORDER BY ID DESC
              LIMIT $limitNumber

Basically, I want to be able to take the on-hand quantity of an inventory item and find the cost of that quantity of the items.

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

For example: If had 30 Forks on hand, and I wanted to know the average cost of the last 30 Forks I purchased, How would I proceed? Obviously the database will be much larger and the idea is to be able to assign a value to on-hand inventory.

UPDATE To make this work I used the following:

SELECT t.ID,
         t.Quantity,
         (t.Cost) as rowTotal,
         @running_total := @running_total + t.Quantity AS cumulative_sum
    FROM Receiving t
    JOIN (SELECT @running_total := 0) r
    WHERE t.Product = '$productID'
    HAVING @running_total <=$numberToReach
ORDER BY t.Date DESC

Then, with PHP I summed the rowTotal and divided by the cumulative_sum

You seem to have two different questions. Do you want a cumulative sum, or the average of the last N rows? — Barmar, Sep 22 '22 at 21:14
Your column headings seem to be all wrong. `Fork` is a product name, not a quantity. — Barmar, Sep 22 '22 at 21:15
I need a cumulative sum to get to the quantity. Each new row will ad to the quantity until it equals or exceeds the on-hand quantity I have. Once I have that, then I know the number of rows in the cost column to average. — Journeyman1900, Sep 22 '22 at 22:30
It's still not clear how the first paragraph and last paragraph fit together. Anyway, see https://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql for calculating cumulative sums. — Barmar, Sep 22 '22 at 22:32
I reworded my question to try to make more sense. Thanks for your patience. — Journeyman1900, Sep 22 '22 at 23:07
The solution in the link you gave did not work in descending order. (select ID, quantity, (@csum := @csum + quantity) as cumulative_sum from Receiving order by ID DESC LIMIT 5) gives null results — Journeyman1900, Sep 22 '22 at 23:46
Works here: https://www.db-fiddle.com/f/vHHDGEhnRebLbYe2JReeuL/0 — Barmar, Sep 22 '22 at 23:57
What database is this "sql" question applicable to? (MySQL version or Postgres or Oracle or whatever) — Brian DeMilia, Sep 23 '22 at 00:35

score 1 · Answer 1 · answered Sep 23 '22 at 16:00

To make this work I used the following:

SELECT t.ID,
         t.Quantity,
         (t.Cost) as rowTotal,
         @running_total := @running_total + t.Quantity AS cumulative_sum
    FROM Receiving t
    JOIN (SELECT @running_total := 0) r
    WHERE t.Product = '$productID'
    HAVING @running_total <=$numberToReach
ORDER BY t.Date DESC

Then, with PHP I summed the rowTotal and divided by the cumulative_sum:

foreach($AVGRun as $AVGResult) { 

    $temp[]=$AVGResult['rowTotal']+0;
    $cumulative_sum=$AVGResult['cumulative_sum'];
}

$AVG=array_sum($temp)/$cumulative_sum;

Sql Query to find average cost of last n rows using PHP

1 Answers1