CODEWITHSUNDEEP
sql-servergroup-bysumwindow-functions
George Eivaz
George Eivaz

Reputation: 145

SQL Server SUM/Group/Window Function

Good day, I have a table as follows.

Before Adding New Column

What I would love to do is add a new Column that will tabulate/summarize (anyway possible) called "New Net" by CovID/PolicyNo/CovYear/Positive(Negative) values.

In the example below the new column would look like this. In short, what we are trying to do is SumUp all the Values in that group and only place that total in the first row of that group and zero out all the others. Any help/pointers would be appreciated with this. I have tried SQL Server Window Functions, standard SUM/GROUP.

enter image description here

Upvotes: 0

Views: 943

Answers (1)

Rafał Wojtaszek
Rafał Wojtaszek

Reputation: 668

This should meet ypur expectations:

SELECT  PolicyNo ,
        CovID , 
        CovYear ,
        p ,
        net, 
        CASE WHEN ROW_NUMBER()OVER(PARTITION BY CovID, PolicyNo, CovYear, net ORDER BY PolicyNo) = 1 THEN net ELSE 0 END AS NewNet
FROM    dbo.test1;

Upvotes: 1

Related Questions