Apparently my Subquery returns more than 1 row-CodePudding

So I want to find all items from the parts table for which the price is greater than or equal to the average price of the respective product line.

And I tried it wirh subquerys and Group by but my Subquery returns more than one row. Any Help?

select * from parts
where price >= (select distinct avg(price)
from parts group by productLine)

CodePudding user response：

You have to create a connection between the parts table and the average prices query, because as you have your subquery now, it returns the average price for all productlines, which you most probably you have more than one ... And also the DISTINCT doesn't help here, unless all of your productlines have the extact same AVG(price) -- which is quite unlikely.

With newer versions of mysql you can use a common table expression

with prices(avgprice, productline) as (
  select avg(price), productline
  from parts 
  group by productline)

select pa.* 
from parts pa inner join prices pr on pa.productline = pr.productline
where pa.price >= pr.avgprice

If you are on a older version of mysql, which doesn't support CTE, you can also join on the result of a subquery

select pa.*
from parts pa inner join (
  select avg(price) as  avgprice, productline
    from parts 
    group by productline) pr on pa.productline = pr.productline
where pa.price >= pr.avgprice

or you can just limit your subquery on the respective productline

select *
from parts p 
where price >= (
  select avg(price) 
  from parts pa 
  where pa.productline = p.productline)