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.
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
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;
- Id starts at 1, hence it can use
salesid ELSE salesid+id+1would work as well
- 0 is considered positive number, hence the
>= 0(Is zero positive or negative?). Although
x+0=xseems 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 numbersmeans
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
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 🙂