MySQL error: Expression of SELECT list is not in GROUP BY clause, nonaggregated column

By Alvin Alexander. Last updated: September 22, 2021

Update: On September 21, 2021, using MySQL 8 and Ubuntu 20.04, the solution to this problem was to edit this file:

/etc/mysql/mysql.conf.d/mysqld.cnf

Then add this configuration line to the end of that file:

sql_mode = ""

Once I did that, the problem described below went away.

Note that you can use this command to show your MySQL version on Ubuntu Linux:

$ mysql -V
mysql  Ver 8.0.26-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))

Background and older solution

NOTE: Everything below this point describes the background to this problem, and solutions I used with older versions of MySQL and Ubuntu Linux.

As a brief note to self, I ran into this MySQL error when running a SQL SELECT and GROUP BY query:

02:14:33.911 [main] ERROR scalikejdbc.StatementExecutor$$anon$1 - SQL execution failed
(Reason: Expression #3 of SELECT list is not in GROUP BY clause and contains
nonaggregated column 'aad8final.u.alias' which is not functionally dependent on columns
in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by):

select tn.nid, count(tn.nid) as countOfTnNid, u.alias, n.title 
from taxonomy_index tn, url_alias u, node_field_data n 
where tn.tid in (select tid from taxonomy_index where nid = 9423) 
and tn.nid = u.nid 
and tn.nid = n.nid 
and n.status = 1 
and n.nid != 9423 
group by tn.nid 
order by countOfTnNid 
desc limit 5

As that error message shows, the problem is that the SQL SELECT query is incompatible with this MySQL setting:

sql_mode=only_full_group_by

I read about a number of possible solutions on Stack Overflow and on the MySQL website. The MySQL site in particular is helpful with these comments:

“this query is illegal in standard SQL-92 because the nonaggregated name column in the select list does not appear in the GROUP BY ... For the query to be legal in SQL-92, the name column must be omitted from the select list or named in the GROUP BY clause.”

So the problem seems to be that MySQL is compatible with SQL-92 by default (and not SQL:1999, which they mention after this). (Or you could say that the problem is with my query.) As a result of reading different possible solutions, I added this line to the /etc/mysql/my.cnf file:

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

After that I restarted MySQL:

sudo service mysql restart

One potential point of confusion is that there are a number of MySQL configuration files under the /etc/mysql directory, as shown by this directory listing:

drwxr-xr-x  4 root root 4096 May 18 03:39 .
drwxr-xr-x 99 root root 4096 May 16 06:50 ..
drwxr-xr-x  2 root root 4096 May  6 06:02 conf.d
-rw-------  1 root root  317 May  6 06:03 debian.cnf
-rwxr-xr-x  1 root root  120 Jan 22  2019 debian-start
lrwxrwxrwx  1 root root   24 Jun 12  2016 my.cnf -> /etc/alternatives/my.cnf
-rw-r--r--  1 root root  839 Apr 20  2016 my.cnf.fallback
-rw-r--r--  1 root root 1105 May 18 03:39 mysql.cnf
drwxr-xr-x  2 root root 4096 May 18 03:38 mysql.conf.d

To get through today’s emergency I added that sql_mode line to the end of the mysql.cnf file, but I encourage you to look at all of the files under that directory. For instance, the mysql.conf.d/mysqld.cnf seems to have most of the actual configuration information in it.

In summary, if you run into the MySQL “SQL execution failed (Reason: Expression .. of SELECT list is not in GROUP BY clause and contains nonaggregated column .. which is not functionally dependent on columns in GROUP BY clause” error, I hope this solution is helpful.

mysql

column