15,793,099 members
1.00/5 (1 vote)
See more:
I am trying to sum profits with avarage price of both purchase price and sale price this is the result that i expect according to calculator:
```24*(24*4)/24-24*(36*2.333+24*2.5+48*2.4)/108
The result is: 38.4026667```

How to get this result in sqlite?

In my SQLite i have the following data:
```In purchases:
Pro ID.    Qnty.  Price
Item1.     36        2.333
Item1.     24        2.5
Item1.     48.       2.4

Total of quantity is 108

In sales :
Pro ID.       Qnty.  Price
Item1          24        4

In products:
Pro ID
Item1```

Updated:

I need to show all rows of data not only one row

What I have tried:

I tried this but I got result that i don't expect:

```SELECT p_name,
sum(sa_quantity)*
(sum(sa_quantity * sa_price) / sum(sa_quantity))
-
sum(sa_quantity)*
(Sum(pur_quantity * pur_price) / Sum(pur_quantity))
As Profits
from sales
join products on
products.p_id=sales.pro_id
join purchases on
purchases.pro_id=sales.pro_id
group by p_name```
Posted
Updated 24-May-23 16:02pm
v6

## Solution 1

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:
SQL
```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:
SQL
```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```

v2
Hello I am showing data in adapter so I don't use conditions i need to show everything all products that having a sale ID can you show an example of that?
I can't understand your example you showed her only one table i have three tables ??
Graeme_Grant 24-May-23 19:12pm
I fixed the part that needed fixing. It does correctly do the calculation for you. I don't have your database, only you do.