Sierra Sierra - 2 years ago 90
SQL Question

MySQL: AVG function: Why does query return incorrect value?

So I ran this small query and I seem to have something strange going on.

I want to display all items that have a stock greater than the average stock in stock (if that makes sense...).

When I run it one of the products has less stock than the calculated average...and I'm not sure why. In this scenario the item returning that should not be is: 421

Return Data:

"sum(quantity_stocked)" "count(product_id)" "avg(quantity_stocked)"
"15885" "18" "882.5000"

"product_id" "quantity_stocked"
"861" "1082"
"591" "1948"
"485" "1402"
"421" "858"
"277" "1920"
"214" "902"
"59" "1021"


select sum(quantity_stocked), count(product_id), avg(quantity_stocked)
FROM products;

SELECT product_id, quantity_stocked
FROM products
WHERE quantity_stocked > (
SELECT avg(quantity_stocked)
FROM products
GROUP BY product_id

Remaining Code:

product_ID int(10) UNIQUE,
product_name varchar(30) NOT NULL,
quantity_stocked int(5),
product_cost dec(8,2) NOT NULL,
PRIMARY KEY (product_ID)

(452,'Nike SS Pro Top',418, 22.99),
(861,'Nike LunarGlide 7',1082, 74.99),
(453,'Nike LS Pro Top',654, 64.99),
(454,'Nike SS Pro Top',720, 64.99),
(451,'Nike Pro Tank',480, 45.99),
(485,'Nike 5k Run Short',1402, 24.99),
(582,'Nike Fit Capri',750, 29.99),
(591,'Nike Be Fast Tight',1948, 39.99),
(847,'Nike Legend Tee',120, 34.99),
(277,'Nike Miller v-neck',1920, 44.99),
(135,'Adidas Soccer Pant',630, 34.99),
(456,'Adidas SS Breathe Top',754, 19.99),
(457,'Adidas Pro Cleat',626, 44.99),
(214,'Adidas Slide',902, 19.99),
(054,'Underarmor SL Top',854,54.99),
(059,'Undersarmor LS High Neck',1021,54.99),
(574,'Underarmor TR Short',746,24.99),
(421,'Underarmor TR Balance Shoe',858,84.99);

Answer Source

In your subquery, you compare to average of one of your products (which is, in this particular example, will be #452, with quantity_stocked of 418), not average of all products. Just remove this part:

GROUP BY product_id
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download