0

Can someone help me sort out the following error:

In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'otpallet_pokemonpalet.p.id'; this is incompatible with sql_mode=only_full_group_by

SELECT p.id, p.question, 
      GROUP_CONCAT(a.answer SEPARATOR ';') AS answers, 
      GROUP_CONCAT(a.id SEPARATOR ';') AS answers_id 
FROM poll p 
JOIN poll_answer a ON p.id = a.poll_id 
WHERE p.status = 1 AND date_start <= NOW() AND date_end >= NOW()

I tried to disable only group mode but I do not have super access in cpanel ...

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
Caique Oliveira
  • 1

1 Answers1

0

Disabling only_full_group_by mode (which is ANSI SQL standard compliant) is not the right approach. You are getting this error because of usage of aggregation function Group_concat() alongside regular columns like p.id and p.question in the Select, with no mention of Group By details.

Due to this aggregation function, you will get a single row (without Group By). But MySQL has no means of identifying which p.id and p.question to return in that one row. Previous MySQL versions (< 5.7) used to be lenient about this (not ANSI standard compliant), but thankfully they have made the stricter mode as default behavior now.

You should really disambiguate your query by adding a Group By clause involving p.id and p.question.

Try the following:

SELECT p.id, p.question, 
      GROUP_CONCAT(a.answer SEPARATOR ';') AS answers, 
      GROUP_CONCAT(a.id SEPARATOR ';') AS answers_id 
FROM poll p 
JOIN poll_answer a ON p.id = a.poll_id 
WHERE p.status = 1 AND date_start <= NOW() AND date_end >= NOW()
GROUP BY p.id, p.question

Also, do check: SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57