Bring results from two tables (INNER JOIN error)

Question

Bring results from two tables (INNER JOIN error)

Navigation

#1 by (2 votes)

3

I have the secretaries table with the following structure:

Andtheworkertablewiththefollowingstructure:

Ilinkedtheworkertabletothesecretariestablethroughthefollowingrelation:

That is, I'm relating the Foreign key to the 'worker' table's primary key id of the 'secretaries' table.

But when executing the following query;

SELECT 
COUNT(trabalhador.id) AS qtdtrabalhadores, 
secretarias.id, 
secretarias.nome, 
secretarias.responsavel 
FROM trabalhador 
INNER JOIN secretarias 
ON trabalhador.id_secretaria = secretarias.id;

... I get this error:

1140 - In aggregated query without GROUP BY, expression # 2 of SELECT list contains nonaggregated column 'cadastro-sumare.secretarias.id'; this is incompatible with sql_mode = only_full_group_by

Could anyone tell me why? Thank you in advance.

php mysql sql phpmyadmin

asked by anonymous 25.06.2017 / 20:55

1 answer

How to sort the position of a matrix automatically? Why is the ZipFile function not working?

score 2 · Accepted Answer

Explaining in a simple way, whenever you use an aggregate function in your SELECT (sum, max, count, min etc), you must put all SELECT fields that are not aggregated into GROUP BY. That way, you are telling the database which fields you want to group the results in.

Your query would look like this:

SELECT 
COUNT(trabalhador.id) AS qtdtrabalhadores, 
secretarias.id, 
secretarias.nome, 
secretarias.responsavel 
FROM trabalhador 
INNER JOIN secretarias 
ON trabalhador.id_secretaria = secretarias.id
GROUP BY secretarias.id, 
secretarias.nome, 
secretarias.responsavel;