Query - Sum with Conditions in Partition Clause?
sql, sql-server
Solution
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.
Problem
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!!!!!!