ERROR #1111 - Invalid use of group Function (SUM | COUNT)

Question

This query gives me the total attendance of two parliamentary groups (e.g: PS, PSD) between two dates:

SELECT DISTINCT a.partido, 

(SELECT COUNT(presencas.assiduidade) FROM presencas, reunioes
 WHERE presencas.assiduidade = 'Presença (P)' 
 AND presencas.id_deputado = a.id_deputado 
 AND reunioes.data_reuniao BETWEEN '2015-10-23' AND '2017-08-30' 
 AND presencas.id_reuniao = reunioes.id_reuniao) AS total_presencas

FROM deputados a WHERE a.partido IN ('PS','PSD');

However, I wanted to add the total attendance per parliamentary group (e.g: PS, PSD) between dates, but the following query gives me error:

SELECT DISTINCT a.partido, 

(SELECT SUM(COUNT(presencas.assiduidade)) FROM presencas, reunioes
 WHERE presencas.assiduidade = 'Presença (P)' 
 AND presencas.id_deputado = a.id_deputado 
 AND reunioes.data_reuniao BETWEEN '2015-10-23' AND '2017-08-30' 
 AND presencas.id_reuniao = reunioes.id_reuniao) as total_presencas

FROM deputados a WHERE a.partido IN ('PS','PSD');

I think this error is due to the two functions together SUM | COUNT.

1111 - Invalid use of group Function

Can you help me? My intention is to do something like:

Answer 1

The grouping functions as COUNT, SUM, AVG... among many others, should be used with grouping of data using the clause GROUP BY, and you are not using to define how will be the grouping and counting of values.

In your case, you need something like:

SELECT deputados.partido, SUM(presencas.assiduidade) AS presenca
FROM deputados
JOIN presencas ON (presencas.id_deputado = deputados.id_deputado )
JOIN reunioes ON (presencas.id_reuniao = reunioes.id_reuniao)
WHERE presencas.assiduidade = 'Presença (P)' 
AND reunioes.data_reuniao BETWEEN '2015-10-23' AND '2017-08-30' 
AND deputados.partido IN ('PS','PSD')
GROUP BY deputados.partido

PS: When making joins, give preference to the explicit use of JOINs, leave the clause WHERE for the necessary data filtering.