In this post, we can discuss, how we can sum positive and negative numbers without subquery.
ID Number
--------------------------
1 -7
2 -8
3 -6
4 -5
5 -4
6 -3
7 3
8 4
9 5
10 6
11 8
12 7
The sum of a positive and negative integer is 33, and -33 respectively.
select SUM(Number) as [Sum] from SumPositiveNegative
Getting the Sum value is 0
select SUM(Number) as [Sum] from SumPositiveNegative where Number<=0
select SUM(Number) as [Sum] from SumPositiveNegative where Number>=0
These two queries return the -33 and 33 results respectively but we need the same result in the same query, not in two separate queries.
Here We can use the <a href="https://www.w3schools.com/sql/sql_case.asp" target="_blank" rel="noreferrer noopener">case when</a> statement to get the required result sets.
select
sum(case when Number>=0 then Number end) [PSum]
,Sum(case when Number<=0 then Number end ) [NSum]
from SumPositiveNegative
The above query will return the expected result.
Click to learn more about SQL server