Aggregation function with condition in partition clause

user_18075685 0
Viewed 126

Suppose I had this following table in SQL Server:

colA colB colC
a1 1 -1
a1 2 -1
a1 3 12
a1 4 2
a1 5 -45

I want to calculate the cumulative sum for col C only in the case of positive result else 0

select *
    , sum(colC) over (partition by colA order by colB asc rows between unbounded preceding and current row) as colD
from tableA

If I am using above query it is resulted as:

colA colB colC colD .
a1 1 -1 -1 -1 = -1
a1 2 -1 -2 -1 + -1 = -2
a1 3 12 10 -2 + 12 = 10
a1 4 2 12 10 + 2 = 12
a1 5 -45 -33 12 + -45 = -33

but what I want is:

colA colB colC colD .
a1 1 -1 0 -1 = -1 (-1 is negative so I want 0)
a1 2 -1 0 0 + -1 = -1 (-1 is negative so I want 0)
a1 3 12 12 0 + 12 = 12
a1 4 2 14 12 + 2 = 14
a1 5 -45 0 14 + -45 = -31 (-31 is negative so I want 0)

How can I achieve this in SQL query?

sql-server sql
2 Answers

You can go for a derived table and apply CASE expression as given below:

declare @t table( colA  char(2), colB   int, colC int)

insert into @t 
values
('a1',1     ,-1 )  
,('a1',2    ,-1 )
,('a1',3    ,12 )
,('a1',4    ,2)
,('a1',5    ,-45);


;with cte_colD as
(
SELECT colA,colB,ColC, CASE WHEN colc < 0 then 0 else colC end as ColD
from @t
where colB =1 
union all
SELECT t.colA,t.colB,t.ColC, CASE WHEN (c.colD + t.colC) <0 then 0 else (c.colD + t.colC) end as ColD
from @t as t
INNER JOIN cte_colD as c
on t.colA = c.colA
and t.colB = c.colB + 1
)
select * from cte_colD
colA colB ColC ColD
a1 1 -1 0
a1 2 -1 0
a1 3 12 12
a1 4 2 14
a1 5 -45 0
user_6349350 
declare @t table( colA  varchar(50), colB   int, colC int)

insert into @t 
values
('a1',1     ,-1 )  
,('a1',2    ,-1 )
,('a1',3    ,12 )
,('a1',4    ,2)
,('a1',5    ,-45);

with t as (
select colA,colB,ColC
,sum(IIF(ColC < 0,0,ColC)) over (partition by colA order by colB asc rows between unbounded preceding and current row) as colD
FROM @t)

select colA,colB,ColC,
IIF(ColC<0,0,ColD) ColD
from t
order by colB
user_147999810
Related