A table with single column is provided with integer values. Task is to create two separate columns with sum of positive and negative values respectively.
Input Table: integer_values
id |
5 |
15 |
25 |
0 |
-20 |
-30 |
Output Table
sum_positive | sum_negative |
45 | -50 |
Explanation :
- Sum of positive numbers ( i.e. 5,15,25) is 45
- Sum of negative numbers ( i.e. -20, -30) is -50
DDLs :
CREATE TABLE integer_values (id INT); INSERT INTO integer_values VALUES (5); INSERT INTO integer_values VALUES (15); INSERT INTO integer_values VALUES (25); INSERT INTO integer_values VALUES (0); INSERT INTO integer_values VALUES (-20); INSERT INTO integer_values VALUES (-30);
Solution :
SELECT SUM(CASE WHEN id >0 THEN id ELSE 0 END) AS sum_positive, SUM(CASE WHEN id <0 THEN id ELSE 0 END) AS sum_negative FROM integer_values;