-1
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.

nbk
  • 45,398
  • 8
  • 30
  • 47
arihant singh
  • 11
  • 1
  • 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 Mar 28 '23 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 Mar 28 '23 at 17:44

1 Answers1

1

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
nbk
  • 45,398
  • 8
  • 30
  • 47
  • Or... use `MAX(name)` in the select list. – The Impaler Mar 28 '23 at 16:35
  • every country has only one name so it will not need a max – nbk Mar 28 '23 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 Mar 28 '23 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 Mar 28 '23 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 Mar 28 '23 at 17:08
  • @BillKarwin I stand corrected. I didn't know MySQL had implemented functional dependencies. – The Impaler Mar 28 '23 at 21:14
  • It was implemented in MySQL 5.7.5 released 2014-09-25. See https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html – Bill Karwin Mar 28 '23 at 21:34
  • I was curious, so I looked up when PostgreSQL implemented detection of functional dependencies for ungrouped columns. It was in in version 9.1, in 2011. – Bill Karwin Mar 28 '23 at 21:40