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

Navigation
2

This query gives me the total attendance of two parliamentary groups (eg 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,Iwantedtoaddthetotalattendancebyparliamentarygroup(eg:PS,PSD)betweendates,butthefollowingquerygivesmeerror:

SELECTDISTINCTa.partido,(SELECTSUM(COUNT(presencas.assiduidade))FROMpresencas,reunioesWHEREpresencas.assiduidade='Presença(P)'ANDpresencas.id_deputado=a.id_deputadoANDreunioes.data_reuniaoBETWEEN'2015-10-23'AND'2017-08-30'ANDpresencas.id_reuniao=reunioes.id_reuniao)astotal_presencasFROMdeputadosaWHEREa.partidoIN('PS','PSD');

IthinkthiserrorisduetothetwofunctionstogetherSUM|COUNT.

  

1111-Invaliduseofgroupfunction

Canyouhelpme?Myintentionistodosomethinglike:

    
asked by anonymous 30.08.2017 / 04:15

1 answer

4

Grouping functions such as COUNT , SUM , AVG ... among many others, should be used with data grouping using the GROUP BY clause, and you are not using it to define 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 WHERE clause for the required filtering of the data.

    
30.08.2017 / 13:11
Pass selected values in a select to a div Question using while