CODEWITHSUNDEEP
sqlpostgresqlaggregate-functionspostgresql-9.1
esem
esem

Reputation: 173

select the sum of positive and negative values: postgres

I have a simple task of computing the sum or average of a user's account balance.

The user may have a negative balance or a positive balance during the month. Here is an example of a user balance during the current month

95.63
97.13
72.14
45.04
20.04
10.63
-29.37
-51.35
-107.55
-101.35
-157.55
-159.55
-161.55

I would like to

  1. choose the negative values, compute their sum/average

  2. choose the positive values, compute their sum/average

  3. represent them in 2 columns

Desired result

340.61      -768.27

When I use the UNION operator, I get two rows. When using CASE.. WHEN.. it groups the balances, and I receive multiple rows.

I have other aggregate functions in my postgres query, so I want each of them to be shown in a separate column. Is there any way to do that?

Upvotes: 5

Views: 9592

Answers (2)

klin
klin

Reputation: 121574

In Postgres 9.1:

select
    sum(case when val >= 0 then val end) as positive,
    sum(case when val < 0 then val end) as negative
from the_data;

An alternative solution for Postgres 9.4+:

select 
    sum(val) filter (where val >= 0) as positive,
    sum(val) filter (where val < 0) as negative
from the_data;

Upvotes: 12

Vao Tsun
Vao Tsun

Reputation: 51446

v=# select sum(case when f < 0 then f end) n, sum(case when f >= 0 then f end) p from s170;
    n    |   p
---------+--------
 -768.27 | 340.61
(1 row)

this?.. why not using case twice?

Upvotes: 5

Related Questions