28

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
26

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?

  • 8
    I fixed the query. Needed UNION ALL as well, not UNION. – ypercubeᵀᴹ Jun 29 '16 at 12:27
24

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. – user2023861 Jun 30 '16 at 15:45
  • 1
    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. – Julien Vavasseur Jun 30 '16 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.