Case SUM Calculation Help

Question

Hi,

I am trying to aggregate my total_bal by using the over partition by, but the logic isn't quite doing what I need it to. The main reason being is the start where I have added in case splits when 0 . This is still needed but by keeping this, it's duplicating the output (as you can see Mort_No 3).

Is there anyway to help aggregate this further, or add in new logic, from the fields captured in the table to show 1 value for record 3?

case splits when 0
then sum(value) over (partition by mort_no , due_date, splits, loc )
else
sum(value) over (partition by mort_no ,due_date, splits , loc) * (splits / 100)
end as total_bal

Answer 1

Hi @Bone_12 ，

We recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

over (partition by mort_no , due_date, splits, loc )

You used four columns in your partition part, but the value of splits and loc for Mort_No 3 were totally different.

So you will always receive the duplicated output (Mort_No 3).

You could have a try to filter out the third row firstly and do the aggregation like below:

;with cte as (  
select * from yourtable  
except   
select * from yourtable  
where Mort_no in (select Mort_no from yourtable where splits<>0) and splits=0)  
select *,  
case splits when 0  
then sum(value) over (partition by mort_no , due_date, splits, loc )  
else  
sum(value) over (partition by mort_no ,due_date, splits , loc) * (splits / 100)  
end as total_bal  
from cte   

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.