Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

SQL Sum with Group By not accepting only one column

I have made this query with a kind of math expression but the result is not what I expected.

The query is:

SELECT c.nome,round((d.montante / SUM(d.montante) OVER()),2)* 100 FROM dado d 
join categoria c on d.categoria_id = c.id where cast(d.tipologia_enum as text)
 =:tipologia and extract(year from d.data) in :anos and extract(month from d.data)
 in :meses group by c.nome,d.montante

The result is:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

nome column
Alimentação e Bebidas 9
Alimentação e Bebidas 20
Alimentação e Bebidas 56
Casa e Serviços 6
Compras 9

The problem is that I want to group by only by the first column and sum the second column but somehow I can’t because it gives me this error:

"SQL Error [42803]: ERROR: column "d.montante" must appear in the
GROUP BY clause or be used in an aggregate function"

Can anybody help me?

>Solution :

If you want a percent by category as the desired output implies you can combine SUM aggregation and SUM window function

SELECT c.nome, round((SUM(d.montante) / SUM(SUM(d.montante)) OVER()), 2) * 100 
FROM dado d 
join categoria c on d.categoria_id = c.id 
where cast(d.tipologia_enum as text)
   =:tipologia and extract(year from d.data) in :anos and extract(month from d.data)
 in :meses 
group by c.nome
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading