Error in nonaggregate column which is not functionally dependent on columns in GROUP BY clause in sql

Error Number: 1055

Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column codelone.tags.post_id which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SELECT `tags`.`tag_slug`, `tags`.`tag`, `tags`.`post_id` FROM `tags`
JOIN `posts` ON `posts`.`id` = `tags`.`post_id` JOIN `users` ON
`posts`.`user_id` = `users`.`id` WHERE `posts`.`status` = 1 AND
`posts`.`visibility` = 1 GROUP BY `tag_slug`, `tags`.`tag` ORDER BY
`tags`.`tag`

Filename: models/Tag_model.php

Line Number: 106

What I tried:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

but I don’t have super privilege’s. Is there have any way?

Answer

Use an aggregation function on the columns not in the GROUP BY. For instance:

SELECT t.tag_slug, t.tag, GROUP_CONCAT(t.post_id)
FROM tags t JOIN
     posts p
     ON p.id = t.post_id JOIN
     users u
     ON p.user_id = u.id
WHERE p.status = 1 AND p.visibility = 1
GROUP BY t.tag_slug, t.tag
ORDER BY t.tag

Advertisement

Answer