10

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

0

4 Answers 4

15

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

2
  • for me it works without the double quotes though I concur with your recommendation (version 10.3)
    – Qsigma
    Apr 26, 2018 at 8:55
  • How can this be extended to multiple columns? I want to query for some criteria, then sort the result set in decreasing order of the number of true values present in a set of Boolean columns. Mar 20, 2023 at 11:33
10

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
 ;
2
  • Does this get optimized if you define a partial index on the boolean column, or does it turn into a linear search, because of the when..then..else expression? Mar 20, 2023 at 11:32
  • @LukeHutchison the query wants to count all rows with "public" and all rows without, and a partial index would only help with the first part. And probably not used at all with the first queries (haven't tested that), as you mentioned the CASE expression would interfere. The last query (with separate subqueries) could certainly use partial indexes. Mar 21, 2023 at 7:46
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
1

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 ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.