Case SUM Calculation Help

361 Reputation points
2021-10-06T16:46:23.24+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,352 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions

1. 24,191 Reputation points
2021-10-07T01:44:35.86+00:00

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