Case SUM Calculation Help

Bone_12 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

138227-image.png

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
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 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.
    138335-partition.png

    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.

    0 comments No comments

0 additional answers

Sort by: Most helpful