15,916,280 members
See more:
```I am getting wrong result from SQLite database when using avg() function!

In my database SQLITE table_name i have the following data:

Item_ID Item_name quantity price

1.     Item1      24      2.333

1.     Item2.     36      2.083

1.    Item3       24      2.25

1.    Item4       12.     2.166

1    Item5        48.     2.416```

What I have tried:

```I am using this query to get average of purchase price in my project :

select sum(quantity) as Quantity, avg(price) as PRICE from table_name where item_ID=1;

The result that i expected is : 2.270416667 but I got 2.2496 instead ...
In calculator or XLS this is what I get : 2.270416667
In calculation i do this to sum average:
(Quantity*price+Quantity*price+Quantity*price...)/Total of quantity
How can I solve the problem to get the right result?```
Posted
Updated 4-May-23 0:55am
v5
Is there any solution to get correct result?
0x01AA 4-May-23 6:51am
Pragmatic approach: `SUM(Quantity * Price) / SUM(Quantity)`
Member 15627495 4-May-23 6:43am
hello !

2.2496 comes from the avg of the 'Price column'. but it's not the full operation. it's just all prices average.

you have to compute 'Quantity * Price' , before apply AVG()
Hi bro how to do that in query?

## Solution 1

The result is correct, and is precisely what you asked for.

The sum of the `price` column is `11.248`; dividing that by the number of rows (`5`) gives `2.2496`, which is the result you are getting.

The only way to get your expected value is to take the average price per item. To do that, you need to sum the product of the quantity and price, and divide by the sum of the quantity:
SQL
`SELECT Sum(quantity) As Quantity, Sum(quantity * price) / Sum(quantity) As Price FROM table_name WHERE item_ID = 1;`

CHill60 4-May-23 7:02am
Beat me to it. 5'd
Thank you so much i accepted you answer i want to know if this is the right and correct average of the price?
Richard Deeming 4-May-23 7:09am
You need to be more specific.

The average of "the price column" is the sum of the values in the price column divided by the number of rows. That is `2.2496`.

The average of "the price per item" is the sum of the total price charged (`price * quantity`) divided by the sum of the number of items. That is `2.270416667`.