# If positive, sum all items. If negative, return each one

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
``````

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?

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
``````
• You can simplify your group clause with an iif like this: `GROUP BY salesid, iif(num >= 0, 0, id)` Cool query. Commented Jun 30, 2016 at 15:45
• Yes but OP would have to first install SQL Server 2012. IIF starts with SQL Server 2012: msdn.microsoft.com/en-us/library/hh213574.aspx. OP has tagged his question with SQL Server 2008. Commented Jun 30, 2016 at 15:48
``````SELECT
sum(profit) as pos_sum
FROM
Sales
WHERE
profit > 0
UNION ALL
SELECT
sum(profit) as neg_sum
FROM
Sales
WHERE
profit < 0;
``````