DJONES DJONES - 2 years ago 83
SQL Question

SQL select sum of column with value in multiple rows

I have a Table in sql, the collumns return PID and Val1 I am trying to add 2 sum columns of certain each PID.

The ValTotal column should be a Sum of every Val with that PID.

So for example, in the select bellow, you can see that the Val1Total and Val2Total are a SUM of the Val1 and Val2 for the PID of 1.

PID Val1 Val1Total
1 10 20 'See how both (1) columns have the same total
2 10 10
1 10 20 'See how both (1) columns have the same total
4 10 10
3 10 10

I have searched the web and cannot find what I am looking for, Is this even something I can do?

Any help or advice would be appreciate, thank you in advance.

Answer Source

Perhaps you are just looking for window functions:

select pid, val1, val2,
       sum(val1) over (partition by pid) as val1total,
       sum(val2) over (partition by pid) as val2total
from t;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download