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

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?

CodePudding user response：

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

CodePudding user response：

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;