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

ID
1
-1
2
-1
3
-3

Expected Output

SumIgnoring-veValues TotalSum +VeSum -VeSum
11 1 6 5

Rules/Restrictions

• The solution should be should use “SELECT” statement or a CTE.

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