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

Question

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

It is so refreshing to see a question on debugging that has all the components in place to make it answerable! — Shadow
– Shadow, Commented Apr 20, 2016 at 0:53
It's something I've been working hard to make happen with my questions! :) Glad it's getting there! — Sierra
– Sierra, Commented Apr 20, 2016 at 1:15

Sych · Accepted Answer · 2016-04-20 00:32:34Z

2

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

answered Apr 20, 2016 at 0:32

Sych

1,85216 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sierra Over a year ago

Thank you very much! That's working so much better! :)

Sierra Over a year ago

If I may ask, how did you figure that it would be comparing with 452? Because it's the top value?

Sych Over a year ago

Because of LIMIT 1 it will take the first row that is caught by SELECT. And what is the first row in your table - I know it from the INSERT statement that you gave.

Sierra Over a year ago

Ah, that makes so much more sense. Thank you again!

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related