Bring results from two tables (INNER JOIN error)

Question

I have the table secretariats with the following structure:

And the worker table with the following structure:

I related the worker table to the secretariats table through the following list:

That is, I am relating the Foreign key id_secretary of the table 'worker' to the Primary Key id of the table 'secretariats'.

However 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;

... 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

Would anyone like to tell me why? I thank you in advance.

Answer 1

Simply put, whenever you use an aggregator function in your SELECT (sum, max, Count, min etc), you should put all SELECT fields that are not aggregated in GROUP BY. That way, you’re telling the database by which fields you want to group the results.

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;