How to count boolean values in PostgreSQL?

Question

How to count, how may true and false for the field public in postgresql user table i have tried this query

select 
sum(case when false then 1 else 0 end) as false, 
sum(case when true then 1 else 0 end) as true
from  public.user;

but am not getting any value and if i remove public from query then i will get correct counts only i have value true

table : name|   DOB     | public
values : bb | 20/2/1991/| true

op : true = 1 and false  = 0

but am getting the same answer when i make public as false

table : name|   DOB     | public
values : bb | 20/2/1991/| false

op : true = 1 and false  = 0

so someone please help me to solve this

Answer 1

Use the filter() clause:

select count(*) filter (where "public") as public_count,
       count(*) filter (where not "public") as not_public_count
from  public."user";

Note that user is a reserved keyword, you have to use double quotes in order to use it as a table name.

The above assumes that the column public is of type boolean

Answer 2

Other ways, that work in older versions that don't have FILTER, using CASE expressions or subqueries:

SUM and CASE expression

select
    sum(case when not public then 1 else 0 end) as false,
    sum(case when     public then 1 else 0 end) as true
from 
    public.user;

COUNT and CASE expression (the default ELSE NULL is omitted)

select
    count(case when not public then 1 end) as false,
    count(case when     public then 1 end) as true
from
    public.user;

SUM after converting the boolean to integer (TRUE -> 1, FALSE -> 0)

select
    sum((not public)::int) as false,
    sum(     public ::int) as true
from
    public.user;

a rather obfuscated solution (using 3VL to convert FALSE to NULL)
(by @Andriy):

select
    count(not public or null) as false,
    count(    public or null) as true
from
    public.user;

a slightly more clear (or more obfuscated?) 3VL abuse:

select
    count(public and null) as false,
    count(public or  null) as true
from
    public.user;

a subquery for each count

select
    (select count(*) from public.user where not public) as false,
    (select count(*) from public.user where     public) as true
 ;

Answer 3

Case expression has two possible forms:

case <field> when <value> then <actions1> else <actions2> end
case when <condition> then <actions1> else <actions2> end

In this clause, the condition is never true

case when false then 1 else 0 end

Instead, use

case public."user".public when false then 1 else 0 end

or

case when public."user".public = false then 1 else 0 end

Answer 4

You don't get proper results because you omitted the column from your expressions:

select 
sum(case when "public" = false then 1 else 0 end) AS false_ct, 
sum(case when "public" = true  then 1 else 0 end) AS true_ct
from  public.user;

There are various simpler and faster techniques. Suggestions in other answers. It's a recurring topic ...

• For absolute performance, is SUM faster or COUNT?
• Return counts for multiple ranges in a single SELECT statement