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

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