CODEWITHSUNDEEP
postgresqlgroup-bysumaggregate-functionswindow-functions
alekscooper
alekscooper

Reputation: 831

SQL aggregate sum produces unexpected output

I don't understand how sum works.

For a PostgreSQL table in dbeaver:

a b c d
1 2 3 2
1 2 4 3
2 1 3 2
2 1 4 2
3 2 4 2

the query

select a, b, c, d, sum(c) as sum_c, sum(d) as sum_d from abc a group by a, b, c, d

produces

a b c d sum_c sum_d
1 2 3 2 3 2
1 2 4 3 4 3
2 1 3 2 3 2
2 1 4 2 4 2
3 2 4 2 4 2

and I don't understand why: I expected sum_c would be 18 in each row, which is the sum of values in c, and sum_d would be 11 for the same reason.

Why do sum_c and sum_d just copy the values from c and d in each row?

Upvotes: 1

Views: 233

Answers (1)

forpas
forpas

Reputation: 164154

You can't get the result that you want with group by.
When you aggregate with group by you create groups for all the columns that are after group by and for each of these groups you get the aggregated results.
For your sample data, one group is 1,2,3,2 and for this combination of values you get the sum of c which is 3 since there is only 1 row with c=3 in that group.

Use SUM() window function:

SELECT a, b, c, d, 
       SUM(c) OVER () sum_c, 
       SUM(d) OVER () sum_d 
FROM abc

Upvotes: 2

Related Questions