SQL Puzzle | Sum of Positive values, Negative values, Total Sum & Sum ignoring -Ve values in a single Query

This question is bit simple. This was asked to me by a colleague. Here we have to find sum of Positive values, Negative values, Total Sum & Sum ignoring -Ve values in a single Query.

For details please check out the sample input and the expected output below-

Sample Input

Expected Output

Rules/Restrictions

• The solution should be should use “SELECT” statement or a CTE.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table & insert some sample data

--

CREATE TABLE SumPositiveNegative
(
 ID INT
)
GO


INSERT INTO SumPositiveNegative VALUES
(1),
(-1),
(2),
(-1),
(3),
(-3)
GO

--

Solution 1

--

SELECT
     SUM(ABS(ID)) 'SumIgnoring-veValues'
    ,SUM(ID) 'TotalSum'
    ,SUM(CASE WHEN ID > 0 THEN ID ELSE 0 END) '+VeSum'
    ,SUM(CASE WHEN ID < 0 THEN ID ELSE 0 END) '-VeSum'
FROM SumPositiveNegative

--

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com