Here is how I would look at it before I create the Sql statement:
item 1. 36 2.333 83.988
item 1. 24 2.57 60
item 1. 48 2.4 115.2
Total 108 259.188
avg purchase price = 259.188 / 108 = 2.399888889
So if sell price is 4.00 * 24 = 96
The total cost is 2.399888889 * 24 = 57.59733333
The profit is 96 - 57.59733333 = 38.40266667
So now rework your Sql statement to use:
WITH avg_price AS (
SELECT AVG(pur_quantity * pur_price) AS avg_pur_price
FROM products
WHERE product_id = item_id // item1
GROUP BY product_id
)
SELECT (sa_quantity * sa_price) - (sa_quantity * avg_pur_price) AS total_profit
FROM avg_price;
Note, this is not tested but should get you there...
I've run a test and this works:
WITH cost AS(
SELECT SUM(quantity * unit_cost) AS tl, SUM(quantity) AS qty
FROM products
WHERE product_id = item_id
)
SELECT(sa_quantity * sa_price) - (cost.tl / cost.qty) * sa_quantity AS total_profit
FROM cost