How to Bring Back GROUP by in MySQL


If you are suddenly not able to perform GROUP BY statements as you used to after a MySQL/MariaDB version update, then you will have noticed the change forced upon you.

You can re-enable the previous GROUP BY behaviour by editing your /etc/mysql/my.cnf and adding in the following line below the [mysqld] section:

[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"

This will allow you to GROUP BY without having to return all those specific columns as well.

Why this happened

It turns out that the original functionality was a trick that we all used to use, and it was removed recently in order to address “incompatibilities”. You can read more about it here:

https://mariadb.com/kb/en/mariadb/group-by-trick-has-been-optimized-away/

This is more of a problem of MySQL having implemented the ANSI standard and then deciding to remove it much later