CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
RBK
RBK

Reputation: 405

Sum column values using filter with selected values only in PostgreSQL

I have a data set as:

Code        | Value0  |  Value1    | Value2     |
------------| --------|----------- | -----------|
CAR         | 44000   | 5.0        | 2          |
CBB         | 1000    | 0.89       | 9          |
CC          | 1450    | 1.56       | 5          |
CIVG        | 21000   | 0.786      | 19         |
CIVR        | 7000    | 134.6      | 15         |
CBB         | 21000   | 440        | 39         |
CC          | 14000   | 16.78      | 35         |
CIVG        | 100     | 98.56      | 9          |
CIVR        | 45000   | 15         | 54         |

I want to aggreagate based on column code, using the filter that Only those rows should be summed which have value0 greater than 10000. However, this exception or filter should only apply to codes CIVG and CIVR. This filter will not apply to the other codes.

The query that I have been able to figure out:

select code , 
       count(1) filter (where value0 > 10000 ) as funt,
       count(1) ount,
       sum(value2) filter (where value0 > 10000 ) "farc" ,
       sum(value2) "oarc"
from table 
group by code
order by code;

Using which, I get the below result:

    Code    | funt |  ount   |   farc      |    oarc  |
------------| -----|---------| ------------|----------|
CAR         | 1    | 1       | 2           |   2      |
CBB         | 1    | 2       | 39          |   48     |
CC          | 1    | 2       | 35          |   40     |
CIVG        | 1    | 2       | 19          |   28     |
CIVR        | 1    | 2       | 54          |   69     |

What I want is that the variables apart from CIVG and CIVR, should not have the filter apply to them, which would give the result something like:

    Code    | funt |   farc      
------------| -----|---------
CAR         | 1    | 2        
CBB         | 2    | 48         
CC          | 2    | 40       
CIVG        | 1    | 19     
CIVR        | 1    | 54

Basically, the filter only works for the value that we provide and rest of the data follows the usual logic of summary. Is this possible using PostgreSQL?

Upvotes: 6

Views: 14227

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Just add a condition to the filter:

select code , 
       count(1) filter (where value0 > 10000 or code not in ('CIVG', 'CIVR')) as funt,
       count(1) ount,
       sum(value2) filter (where value0 > 10000 or code not in ('CIVG', 'CIVR')) "farc" ,
       sum(value2) "oarc"
from table 
group by code
order by code;

Upvotes: 10

Related Questions