sql_mode=only_full_group_by in query statement

Question

found sql_mode=only_full_group_by error in query.I have tried many ways to fix it.changed sql_mode=''.still I get this error.

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'intranet.a.tagid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by:

I have given the table fields

SELECT a.tagid,b.tagword,a.metric 
from nextag_metrics a 
    LEFT JOIN nextag_words b ON a.tagid=b.id 
WHERE b.tagword is not NULL 
AND type='filedepot' 
OR groupid in (2931,2938,2941) 
OR userid in (1) 
OR categoryid in (2,3,4,5,6,8,9,10,11) 
GROUP BY b.tagword 
ORDER BY b.tagword ASC

how about fixing the query itself? You need to group by all projected fields that are not aggregated. If you don't understand why, you probably haven't really figured the very problem you're trying to solve either. — Sebas
That GROUP BY is invalid, will raise an error on newer MySQL versions (unless in compatibility mode), and return an unpredictable result on older MySQL versions. The general GROUP BY rule says: If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function! — jarlh

Gordon Linoff Gordon Linoff · Accepted Answer · 2017-04-24T12:31:31

This is your query:

SELECT m.tagid, w.tagword, m.metric 
FROM nextag_metrics m LEFT JOIN
     nextag_words w
     ON m.tagid = w.id
WHERE w.tagword is not NULL AND m.type = 'filedepot' OR
      m.groupid in (2931, 2938, 2941) OR
      m.userid in (1) OR
      m.categoryid in (2,3,4,5,6,8,9,10,11)
GROUP BY w.tagword
ORDER BY w.tagword ASC;

There are other problems with this query. First, for your particular problem, don't break the database (only full group by is how the database should work). Fix the query.

Here are some other issues and potential issues:

The WHERE clause is suspicious. I'm guessing it doesn't do exactly what you want.
Aggregating on the 2nd table in a left join is silly under most circumstances. The value might be NULL.
It is unclear which row tagid and metric should come from.

I would suggest that you ask another question. provide sample data (as a text table in the query) and clearly explain what you are trying to do.

sql_mode=only_full_group_by in query statement

1 Answers