What is the error in SQL query (MAX and AVG)?

Question

SQL

Hello, there is a database with two tables, from it you need to pull out a table indicating the manufacturer with the maximum average cost of phones
; `companyId`) AS reporttable INNER JOIN company ON reporttable.companyId=company.companyId " gives the wrong result, namely, it incorrectly indicates the phone manufacturer, while without INNER JOIN it works correctly, indicating the manufacturer's Id and the maximum average price of phones. But you need to display the name of the manufacturer in the table, not its Id. Database file https://disk.yandex.ru/d/lQnRbz4WqZKDHQ

Answer 1

You need to use the following query:

SELECT 
  companyName, 
  PriceAvg 
FROM 
  (
    SELECT 
      `companyId`, 
      AVG(`price`) AS PriceAvg 
    FROM 
      phone 
    GROUP BY 
      `companyId`
  ) AS reporttable 
  INNER JOIN company ON reporttable.companyId = company.companyId
  ORDER BY PriceAvg DESC LIMIT 1;

Check SQL online