I know this is a MySQL error but I am extremely confused as to why this is happening.
This is the code that is giving me the error
(new Query())
->select('soti_list_user.*, SUM(views) as TOT_VIEWS')
->from('stats')
->where(['char_length(stats.unique_key)' => 8])
->where("stat_date > DATE_SUB(NOW(), INTERVAL 1 DAY)")
->innerJoin('soti_list_user', 'soti_list_user.unique_key = stats.unique_key')
->groupBy('stats.unique_key')
->orderBy('TOT_VIEWS DESC')
->limit(100)
->all();
This is the error: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘cmt.soti_list_user.ind’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
The SQL being executed was: SELECT soti_list_user.*, SUM(views) as TOT_VIEWS FROM stats INNER JOIN soti_list_user ON soti_list_user.unique_key = stats.unique_key WHERE stat_date > DATE_SUB(NOW(), INTERVAL 1 DAY) GROUP BY stats.unique_key ORDER BY TOT_VIEWS DESC LIMIT 100
Now here is why I am confused. The following code (which is damn near identical) executes just fine.
(new Query())
->select('thoughts_list.*, SUM(views) as TOT_VIEWS')
->from('stats')
->where(['char_length(stats.unique_key)' => 7])
->where("stat_date > DATE_SUB(NOW(), INTERVAL 1 DAY)")
->innerJoin('thoughts_list', 'thoughts_list.unique_key = stats.unique_key')
->groupBy('stats.unique_key')
->orderBy('TOT_VIEWS DESC')
->limit(100)
->all();
Both commands pull from table ‘stats’ and cross references either ‘thoughts_list’ or ‘soti_list_user’.
‘thoughts_list’ and ‘soti_list_user’ are almost identical in structure. The only key difference is that column ‘unique_key’ is 7 characters in ‘thoughts_list’ and 8 characters in ‘soti_list_user’.
I have spent hours scouring the net and checking my table structures to figure out why I’m getting this error and I’m drawing a blank. I do not understand what “nonaggregated column” means, especially given the similar structure of ‘thoughts_list’.
I really want to understand why this error is triggered on ‘soti_list_user’ and not on ‘thoughts_list’ as well as a fix.
Some insight would greatly be appreciated.
