CODEWITHSUNDEEP
sqlsql-server
John Bustos
John Bustos

Reputation: 19544

Query - Sum with Conditions in Partition Clause?

Suppose I had this 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 was looking to achieve (in words) woud be: Create a new column, Conditional_Sum = Sum of ColC for each value in ColA where ColB = 1 and ColC > 0

So, the final result would be (added in 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 kind of result in my dataset??

Thanks!!!!!!

Upvotes: 16

Views: 42040

Answers (1)

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174279

Try this:

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

My answer assumes that the conditional sum for rows 7 to 9 is supposed to be 400.

Upvotes: 48

Related Questions