Search code examples
sqlpostgresqlgroup-bysum

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:

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