Request - the amount with the conditions in the section section?

Question

Suppose I had the following table in SQL Server:

ColA ColB ColC A 1 100 A 0 -100 A 1 -100 A 0 100 A 1 100 A 1 -100 B 1 200 B 1 -200 B 1 200 C 0 300 C 1 -300 

And what I wanted to achieve (in words) would be: Create a new column Conditional_Sum = Sum ColC for each value in ColA , where ColB = 1 and ColC > 0

So, the end result will be (added to RowNum to show how I got the values):

 Rownum ColA ColB ColC Conditional_Sum 1 A 1 100 200 (rows 1+5) 2 A 0 -100 200 (rows 1+5) 3 A 1 -100 200 (rows 1+5) 4 A 0 100 200 (rows 1+5) 5 A 1 100 200 (rows 1+5) 6 A 1 -100 200 (rows 1+5) 7 B 1 200 400 (rows 7+9) 8 B 1 -200 400 (rows 7+9) 9 B 1 200 400 (rows 7+9) 10 C 0 300 0 (no rows match) 11 C 1 -300 0 (no rows match) 

So my pseudo-SQL for this would be:

 Sum(ColC) Over(Partition By ColA, ColB=1,ColC>0) as Conditional_Sum 

How can I achieve this result in my data set?

Thanks!!!!!!

Answer 1

Try the following:

 Sum(case when ColB = 1 and ColC > 0 then ColC else 0 end) over(partition by ColA) 

My answer assumes that the notional sum for lines 7 through 9 should be 400.