Cumulative sum of a column in two different positive and negative directions

Question

how can I get desired result. I want to calculate the cumulative sum of negative numbers first and then add this cumulative sum with positive numbers.

CREATE TABLE dbo.tb(
    [group_name] [nvarchar](255) 
    ,[value] [float] 
)
INSERT INTO [dbo].[tb]
           ([group_name]
           ,[value])
     VALUES
           ('A',-3),('A',-2),('A',-1),('A',1),('A',3),('B',-2)

By writing the following code, the result column will be displayed

SELECT [group_name]
      ,[value]
      ,sum(value) OVER (PARTITION BY group_name ORDER BY value asc ) result
  FROM [dbo].[tb]

How can I achieve the desired column?

group_name	value	result	desired
A	-3	-3	-6
A	-2	-5	-3
A	-1	-6	-1
A	1	-5	0
A	3	2	3
B	-2	-2	-2

Answer 1

You may use conditional sum window function as the following:

SELECT group_name, value,
       CASE 
         WHEN 
          value<0 THEN
            SUM(CASE WHEN value<0 THEN value END) OVER 
            (PARTITION BY group_name ORDER BY value DESC)
          ELSE
            SUM(CASE WHEN value>=0 THEN value END) OVER 
            (PARTITION BY group_name ORDER BY value) + 
            MAX(CASE WHEN value<0 THEN value END) OVER 
            (PARTITION BY group_name)
       END AS desired
FROM tb
ORDER BY group_name, value

See a demo.