Click here to Skip to main content
15,916,280 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Comments
brahim farhat (AAD) 4-May-23 6:36am    
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()
brahim farhat (AAD) 4-May-23 6:47am    
Hi bro how to do that in query?

1 solution

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;
 
Share this answer
 
Comments
CHill60 4-May-23 7:02am    
Beat me to it. 5'd
brahim farhat (AAD) 4-May-23 7:06am    
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.
brahim farhat (AAD) 4-May-23 7:21am    
Ok thanks again in my project the importent thing is to get the right amount of total and profits and this what I got in: 326.94 if i sum every each item with it's price thank you

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900