SELECT list is not in GROUP BY clause and contains nonaggregated column

Question

Receiving the following error:

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

When running the following query:

select countrylanguage.language, country.code, sum(country.population*countrylanguage.percentage/100)
from countrylanguage
join country on countrylanguage.countrycode = country.code
group by countrylanguage.language
order by sum(country.population*countrylanguage.percentage) desc ;

Using the MySQL world test database (http://dev.mysql.com/doc/index-other.html). No idea why this is happening. Currently running MYSQL 5.7.10.

Any ideas??? :O

You have the `ONLY_FULL_GROUP_BY` option enabled, which removes MySQL's relaxed rules about `GROUP BY`. — Barmar, Jan 21 '16 at 00:42

davejal · Answer 1 · 2016-01-27T13:44:21.187

31

As @Brian Riley already said you should either remove 1 column in your select

select countrylanguage.language ,sum(country.population*countrylanguage.percentage/100)
from countrylanguage
join country on countrylanguage.countrycode = country.code
group by countrylanguage.language
order by sum(country.population*countrylanguage.percentage) desc ;

or add it to your grouping

select countrylanguage.language, country.code, sum(country.population*countrylanguage.percentage/100)
from countrylanguage
join country on countrylanguage.countrycode = country.code
group by countrylanguage.language, country.code
order by sum(country.population*countrylanguage.percentage) desc ;

edited Jan 27 '16 at 13:44

answered Jan 21 '16 at 00:32

davejal

6,009
10
39
82

32

or just run from the mysql command line: SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); – Norman Bird Feb 18 '18 at 20:18
This runs but produces no effect – Patrick Mutuku Aug 13 '18 at 16:37
2

I had to additionally set it on the session: `SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));` – blindsnowmobile May 24 '20 at 23:31

score -3 · Answer 2 · edited Mar 15 '18 at 14:48

-3

country.code is not in your group by statement, and is not an aggregate (wrapped in an aggregate function).

https://www.w3schools.com/sql/sql_ref_sqlserver.asp

edited Mar 15 '18 at 14:48

Derrick Moeller

4,808
2
22
48

answered Jan 21 '16 at 00:30

Brian Riley

926
1
7
12

5

The link is not working anymore, try to post actual content in place of hyperlinks while answering. – amrendra Dec 18 '17 at 06:18
This is not a question about SQL Server anyway. – reinierpost Feb 20 '20 at 17:01

SELECT list is not in GROUP BY clause and contains nonaggregated column

2 Answers2