CODEWITHSUNDEEP
sqlpostgresql-9.2
Souvik Ray
Souvik Ray

Reputation: 3038

How to apply different conditions for same column and output as new columns in Postgresql?

I have a Postgres table that looks like below

       ip        |          up_score 
-----------------+-------------------
 223.110.181.122 |                 1
 242.123.249.85  |                 0
 10.110.11.1     |                 1
 10.254.253.1    |                 1
 19.7.40.40      |                 0
 242.123.249.85  |                 1
 10.110.11.1     |                 1
 19.7.40.40      |                 0
 10.254.253.1    |                 0
 223.110.181.122 |                 0
 19.7.40.40      |                 0
 10.254.253.1    |                 1

Now I want a separate count of 0s and 1s per ip. I tried the queries below

select ip, count(up_score) from net_score where up_score = 0 group by ip;

select ip, count(up_score) from net_score where up_score = 1 group by ip;

But I want to combine these two queries together such that on a single execution I get the below result

       ip        |    count_1 |    count_0    
-----------------+------------+-----------
 223.110.181.122 |          1 |          1   
 242.123.249.85  |          1 |          1
 10.110.11.1     |          2 |          0
 10.254.253.1    |          2 |          1
 19.7.40.40      |          0 |          3

How can I do this?

Upvotes: 1

Views: 179

Answers (2)

w08r
w08r

Reputation: 1814

You could use a filter clause, something like this (untested):

select ip, 
       count(*) filter (where up_score = 0) AS count_0,
       count(*) filter (where up_score = 1) AS count_1
from net_score group by ip;

edit: unfortunately above does not work for postgres <9.4

Upvotes: 1

Souvik Ray
Souvik Ray

Reputation: 3038

Thanks to @w08r for his solution, but I found a simpler solution here (https://dba.stackexchange.com/a/112797/258199) that uses case expression. I modified it for my own use and used it. I am posting the query below

SELECT ip, 
COUNT(case when up_score = 0 
then ip end) as count_0, 
COUNT(case when up_score = 1 
then ip end) as count_1 
FROM net_score 
GROUP BY ip;

Upvotes: 0

Related Questions