SELECT list is not in GROUP BY clause error

Question

Apologies if this is off topic but I'm sure someone here can enlighten me with an answer. I have a MySQL query that works just fine at my host. I have just installed LAMP on my home computer and when I test this query in PHPadmin it fails with this error:

#1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test_table.p.user_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I'm running PHP 5.6 on Apache 2.

This is my working query

SELECT up.file,p.user_name,p.user_id, GROUP_CONCAT(c.collab_username)
FROM tbl_uploads up
LEFT JOIN tbl_users p ON up.user_id = p.user_id
LEFT JOIN tbl_collab c ON up.file = c.file
GROUP BY up.file ORDER BY up.date DESC

Can anyone tell me what this error means?

http://stackoverflow.com/a/38002876/4248328 – Alive to die - Anant Feb 01 '17 at 20:03 — Alive to die - Anant, Feb 01 '17 at 20:03

score 0 · Answer 1 · answered Feb 01 '17 at 20:10

startin for mysql 5.7 you can't (by default) use non aggrateted column if are not in group by clause ,
because this produce not preictable result for not aggreagted cols or you configure your mysql in proper way setting the correct sql_mode or

a simple way is add aggregated function for all the cols not in group by eg:

SELECT up.file, min(p.user_name) , min(p.user_id), GROUP_CONCAT(c.collab_username)
FROM tbl_uploads up
LEFT JOIN tbl_users p ON up.user_id = p.user_id
LEFT JOIN tbl_collab c ON up.file = c.file
GROUP BY up.file 
ORDER BY up.date DESC

SELECT list is not in GROUP BY clause error

1 Answers1