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

Since `price_date` field appears inside an aggregate function, all other fields selected by the query should also appear in an aggregate function. This is what the error message essentially says. — Giorgos Betsos, Oct 06 '17 at 12:59
Check [this answer](https://stackoverflow.com/a/46561721/4265352), [this answer](https://stackoverflow.com/a/46422938/4265352) and [this answer](https://stackoverflow.com/a/46323030/4265352) on similar questions. — axiac, Oct 06 '17 at 13:08
Why do you remove the `GROUP BY` clause? Do you want to see the rows before aggregation? Then also remove the aggregation functions, i.e. change `MAX(price_date) price_date` to `price_date`. — Thorsten Kettner, Oct 06 '17 at 13:39
Possible duplicate of [Error related to only\_full\_group\_by when executing a query in MySql](https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql) — philipxy, Oct 07 '17 at 07:40
Next time please read the pages with relevant keywords (eg GROUP BY) in the official documentation. Also google stackoverflow for them and/or relevant tags and/or error messages. Also read the many suggested duplicates on composition when you use one of those many clear, concise, specific question/problem/desiterata phrasings as title. This is a faq, and since it's clear in the manual, it's an unhelpful faq. Also, please read & act on [mcve]. PS Why are you removing things? Did you not write what you wrote because it did what you want? Did you not read what GROUP BY does? (Rhetorical.) — philipxy, Oct 07 '17 at 07:43

score 1 · Accepted Answer · answered Oct 06 '17 at 12:58

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.

answered Oct 06 '17 at 12:58

nos

223,662
58
417
506

score 1 · Answer 2 · answered Oct 06 '17 at 12:59

Whenever you run an aggregate query (e.g. SUM, MAX, MIN...) SQL needs to group all of the results by the columns that aren't aggregated. So, it requires you to GROUP BY all columns that aren't aggregated. In your case, this would be measure_unit_id, currency_id, and market_commodity_details_id.

What were you trying to achieve by removing the GROUP BY?

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

2 Answers2