Sierra Sierra - 7 months ago 13
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"


Queries:

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
LIMIT 1
);


Remaining Code:

CREATE TABLE products
(
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)
);

INSERT INTO products VALUES
(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

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
LIMIT 1