## All we need is an easy explanation of the problem, so here it is.

I am needing to find a way to `SUM()`

all of the positive values for `num`

and return the `SUM()`

of all positive numbers and an individual row for each negative number. Below is a sample DDL:

```
Create Table #Be
(
id int
, salesid int
, num decimal(16,4)
)
Insert Into #BE Values
(1, 1, 12.32), (2, 1, -13.00), (3, 1, 14.00)
, (4, 2, 12.12), (5, 2, 14.00), (6, 2, 21.23)
, (7, 3, -12.32), (8,3, -43.23), (9, 3, -2.32)
```

And this is my desired output (positive numbers for each salesid `SUM()`

and negatives get an individual line returned):

```
salesid num
1 26.32
1 -13.00
2 47.35
3 -12.32
3 -43.23
3 -2.32
```

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 1

Try this:

```
SELECT salesid, sum(num) as num
FROM #BE
WHERE num > 0
GROUP BY salesid
UNION ALL
SELECT salesid, num
FROM #BE
WHERE num < 0;
```

If you want both the `sum`

values in one row then you must create a `maxValue`

(and `minValue`

) function and use this as `sum(maxValue(0, num))`

and `sum(minValue(0, num))`

. This is described in: Is there a Max function in SQL Server that takes two values like Math.Max in .NET?

### Method 2

This works too:

```
SELECT salesid, SUM(num)
FROM #BE
GROUP BY salesid, CASE WHEN num >= 0 THEN 0 ELSE id END;
```

Assumptions:

- Id starts at 1, hence it can use
`THEN 0`

.`salesid ELSE salesid+id+1`

would work as well - 0 is considered positive number, hence the
`>= 0`

(Is zero positive or negative?). Although`x+0=x`

seems to make the`=`

sign unnecessary, it helps remember that this case has not been forgotten and how 0 is handled (as a SUM or as an individual row). If`the SUM() of all positive numbers`

means`SUM of strictly positive numbers`

(i.e. >0), then`=`

is not needed.

It must be tested with real data and indexes, but with only 1 table scan, performances may be a little better in some cases.

The absence of an index seems to have a smaller impact with this query on test data below:

```
SET NO COUNT ON
Create Table #Be(
id int identity(0,1)
,salesid int,num decimal(16,4)
)
INSERT INTO #BE(salesid, num)
SELECT CAST(rand()*10 as int), rand() - rand()
GO 10000 -- or 100.000
```

### Method 3

```
SELECT
sum(profit) as pos_sum
FROM
Sales
WHERE
profit > 0
UNION ALL
SELECT
sum(profit) as neg_sum
FROM
Sales
WHERE
profit < 0;
```

**Note: Use and implement method 1 because this method fully tested our system.Thank you 🙂**

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0