0 
select countrycode,name, max(population) 
from city 
group by CountryCode LIMIT 0, 1000

Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.city.Name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 0.000 sec

Help me out with this problem.

Improve this question
2
  • 4
    Error message says column name not in group by clause and not an aggregate. You can't do that. Put name into group by clause.
    – markalex
    Commented Mar 28, 2023 at 16:21
  • The old GROUP BY rule says: GROUP BY all columns you SELECT - except those who are arguments to set functions. (And you'll never go wrong.)
    – jarlh
    Commented Mar 28, 2023 at 17:44

1 Answer 1

Reset to default
2

As the error message says you need for all columns in the select a aggregation function or they have to be in the GROUP BY

in your case put it also in the GROUP BY

select countrycode,name, max(population) 
from city 
group by CountryCode,name LIMIT 0, 1000
Improve this answer
8
  • Or... use MAX(name) in the select list.
    – The Impaler
    Commented Mar 28, 2023 at 16:35
  • every country has only one name so it will not need a max
    – nbk
    Commented Mar 28, 2023 at 16:38
  • I would conceptually agree if country_code is a PK. However, MySQL does not implement functional dependency, so MAX() or GROUP BY is needed. This would not be needed in PostgreSQL that does implement it.
    – The Impaler
    Commented Mar 28, 2023 at 16:41
  • yes linked in my answer to the aggregation function, which lead to even more information and i explained the full group by, i think that should explain everything there is to know
    – nbk
    Commented Mar 28, 2023 at 16:59
  • 2
    @TheImpaler Both MySQL and PostgreSQL can infer functional dependencies, but neither can make that inference if the grouping is on a non-unique column.
    – Bill Karwin
    Commented Mar 28, 2023 at 17:08

Not the answer you're looking for? Browse other questions tagged or ask your own question.