question

Bone12-2270 avatar image
0 Votes"
Bone12-2270 asked Viorel-1 answered

SUM Over Partition


Hi,

I have the below SUM that almost gives me what I need, but not fully and wondered if anyone can help please.

sum(c.comp_amount) over (partition by a.comp_number , c.start_date, [TD_numeric] ) * ([TD_numeric]/100) as comp_value

The problem I have is the variable [TD_numeric] having '0' values so when looking to * [TD_numeric] where it has a 0, it's obviously not going to return any values. However, I need it to essentially include an else, so where [TD_numeric] is '0' then bring back this calculation:

sum(c.comp_amount) over (partition by a.comp_number , c.start_date)

Any idea if this can be done please?




sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

Your description can be implemented in this manner:

case TD_numeric when 0 then ... else ... end as comp_value

Replace '...' with corresponding sums.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.