Why am I obtaining this error message removing the GROUP BY statment from a SQL query?

Question

I am not so into SQL and I have the following doubt related to this MySql query:

SELECT x.*
  FROM Market_Commodity_Price_Series x
  JOIN 
     ( 
     /**
                RETURN THE LATEST INFO:
     **/
        SELECT measure_unit_id 
            , currency_id 
            , market_commodity_details_id
            , MAX(price_date) price_date
         FROM Market_Commodity_Price_Series  AS MCPS 
         INNER JOIN MarketDetails_CommodityDetails AS MD_CD ON MCPS.market_commodity_details_id = MD_CD.id
         INNER JOIN MarketDetails AS MD ON MD_CD.market_details_id = MD.id 
         INNER JOIN CommodityDetails AS CD ON MD_CD.commodity_details_id = CD.id
         WHERE MD.id = 4
         AND CD.id = 4
        GROUP BY measure_unit_id, currency_id, market_commodity_details_id
     ) y
    ON y.measure_unit_id = x.measure_unit_id
   AND y.currency_id = x.currency_id
   AND y.market_commodity_details_id = x.market_commodity_details_id
   AND y.price_date = x.price_date;

Why removing the GROUP BY measure_unit_id, currency_id, market_commodity_details_id statment from the joined "table" I obtain the following error message?

#42000In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'digital_services_DB.MCPS.measure_unit_id'; this is incompatible with sql_mode=only_full_group_by

Answer 1

That is how the SQL language is defined.

If you select any aggregates (SUM, MAX etc.) in addition to any non-aggregates you MUST include a GROUP BY clause on all the non-aggregate columns.