So let's fully understand, Let's say you have a query which works in localhost but does not in production mode, This is because in MySQL 5.7 and above they decided to activate the sql_mode=only_full_group_by by default, basically it is a strict mode which prevents you to select non aggregated fields.
Here's the query (works in local but not in production mode) :
SELECT post.*, YEAR(created_at) as year
FROM post
GROUP BY year
SELECT post.id, YEAR(created_at) as year // This will generate an error since there are many ids
FROM post
GROUP BY year
To verify if the sql_mode=only_full_group_by is activated for, you should execute the following query :
SELECT @@sql_mode; //localhost
Output : IGNORE_SPACE, STRICT_TRANS, ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
(If you don't see it, it means it is deactivated)
But if try in production mode, or somewhere where it gives you the error it should be activated:
SELECT @@sql_mode; //production
Output: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO...
And it's ONLY_FULL_GROUP_BY we're looking for here.
Otherwise, if you are using phpMyAdmin then go to -> Variables and search for sql_mode
Let's take our previous example and adapt it to it :
SELECT MIN(post.id), YEAR(created_at) as year //Here we are solving the problem with MIN()
FROM post
GROUP BY year
And the same for MAX()
And if we want all the IDs, we're going to need:
SELECT GROUP_CONCAT(post.id SEPARATOR ','), YEAR(created_at) as year
FROM post
GROUP BY year
or another newly added function:
SELECT ANY_VALUE(post.id), YEAR(created_at) as year
FROM post
GROUP BY year
?? ANY_VALUE does not exist for MariaDB
And If you want all the fields, then you could use the same:
SELECT ANY_VALUE(post.id), ANY_VALUE(post.slug), ANY_VALUE(post.content) YEAR(created_at) as year
FROM post
GROUP BY year
? To deactivate the sql_mode=only_full_group_by then you'll need to execute this query:
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
Sorry for the novel, hope it helps.