CODEWITHSUNDEEP
sqlsql-serversum
daniel aagentah
daniel aagentah

Reputation: 1702

SQL select sum of column with value in multiple rows

I have a table in SQL Server, the columns 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.

Upvotes: 0

Views: 2011

Answers (2)

Raghavendra Bathula
Raghavendra Bathula

Reputation: 21

You can do that using OLAP functions (Window functions) in SQL. But I am not sure if MySQL supports them. Check if Mysql supports SUM() OVER(PARTITION BY ... ORDER BY ...)

If not you can achieve that using joins on the same table. Sample code is given below:

select  A.PID,
    A.Val1,
    A.Val2,
    B.Val1Total,
    B.Val2Total,
From    Table A
Inner Join (Select PID, sum(Val1) as Val1Total, sum(Val2) as Val2Total
        From Table) B
on A.PID = B.PID

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

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;

Upvotes: 2

Related Questions