Error running mysql php query

Question

I am getting the following error while executing the query

1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'protetic_sistema.cad_trabalho.id_trabalho' which is not functionally dependent on Columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SELECT *,ANY_VALUE(cad_trabalho.id_trabalho) as trabalho, DATE_FORMAT(data_saida_trabalho, '%d/%m/%Y') AS data_saida, DATE_FORMAT(cad_estagio.data_estagio, '%d/%m/%Y') AS data_estagio FROM cad_trabalho
JOIN cad_paciente ON cad_trabalho.id_paciente = cad_paciente.id_paciente
JOIN cad_cliente ON cad_paciente.id_cliente = cad_cliente.id_cliente
LEFT JOIN cad_estagio ON cad_trabalho.id_trabalho = cad_estagio.id_trabalho and cad_estagio.id_estagio = (select max(id_estagio) from cad_estagio where id_trabalho = cad_trabalho.id_trabalho)
WHERE cad_trabalho.pronto_trabalho = '0000-00-00'
GROUP BY cad_trabalho.id_paciente
ORDER BY cad_trabalho.data_saida_trabalho ASC

and when I add

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

my system gets very slow.

Answer 1

As already commented all the columns that are in your SELECT need to be in your GROUP BY. If you don’t want to do this you can disable the sql_mode.

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

Answer 2

You must bring in SELECT all the columns that are being passed in the GROUP BY.

Try to add in selection(SELECT), the columns being grouped(GROUP BY).

Or just disable your mysql server, the directive ONLY_FULL_GROUP_BY, which is responsible for restricting this type of consultation:

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

I hope this helps!