SQL Partition By to calculate Net Value

Question

Imagine an order system, with multiple orders having sides as Buy and Sell. The data set look like this

I want to calculate the Net Value which is (Sum of All Buy Sides - Sum of All Sell Sides)

Expected output:

I have currently used Partition By to arrive at following output

SELECT 
[NAME]
,[Side]
,[Value]
,SUM([Value]) OVER (PARTITION BY [NAME],[Side]) as Net
FROM 
[Order]

Output:

But I am not getting how to subtract the values and arrive at the expected result.

Answer 1

You can use window functions with conditional logic

select
    t.*,
    sum(case side when 'B' then value when 'S' then -value end) 
        over(partition by name) net
from mytable t