postgresql - sql - count of `true` values

Question

myCol
------
 true
 true
 true
 false
 false
 null

In the above table, if I do :

select count(*), count(myCol);

I get 6, 5

I get 5 as it doesn't count the null entry.

How do I also count the number of true values (3 in the example)?

(This is a simplification and I'm actually using a much more complicated expression within the count function)

Edit summary: I also want to include a plain count(*) in the query, so can't use a where clause

Does 't' stand for True anf 'f' for False? Or are you looking for something like SELECT COUNT(DISTINCT myCol). — Shamit Verma, Mar 22 '11 at 19:16
take a look at my second example, you can throw a `WHERE myCol = true` in there if you want and if you remove the first `*,` it'll just return the number. — vol7ron, Mar 22 '11 at 19:41
@Shamit yes t stands for true, and f stands for false, I've updated the question — EoghanM, Mar 22 '11 at 20:23
You might as well not simplify your question/query... your requirements restrict the better performance possibilities and people are responding with inefficient answers, which are getting bumped up for no good reason. — vol7ron, Mar 23 '11 at 00:18
@vol7ron in my defense there has to be some simplification in order to ask a comprehensible question, but yes, I oversimplified when I originally posted. — EoghanM, Mar 24 '11 at 17:26
@EgohanM: =] I do it all the time, but when you start saying "can't use `where`" your simplification is not exact. Besides, if you're using a subquery anyhow, you can use a WHERE. The better thing to do would be to use a `join` — vol7ron, Mar 24 '11 at 23:32
The new `filter` approach from [Ilja's answer](https://stackoverflow.com/a/37334318/918959) should be more widely known — Antti Haapala -- Слава Україні, Dec 23 '17 at 20:46

Daniel · Accepted Answer · 2016-05-14T07:17:36.027

194

SELECT COALESCE(sum(CASE WHEN myCol THEN 1 ELSE 0 END),0) FROM <table name>

or, as you found out for yourself:

SELECT count(CASE WHEN myCol THEN 1 END) FROM <table name>

edited May 14 '16 at 07:17

answered Mar 22 '11 at 19:29

Daniel

27,718
20
89
133

2

also, any reason why you did sum(.. THEN 1 ELSE 0) instead of count(.. THEN true else null) ? – EoghanM Mar 22 '11 at 20:32
5

No... it is just that I wasn't sure which values would count() count... and I knew that sum did the trick. But beware: On second thought I believe that sum() over only null values will return null, so it should be COALESCE(sum(...),0) for you, or, in other words, count() is better, – Daniel Mar 22 '11 at 21:38
Thankyou! Never thought my query would work in a million years! – user1158559 Dec 04 '12 at 18:15
1

@EoghanM, see shorter answer involving cast. – Dwayne Towell Jan 03 '14 at 18:10
2

You can actually omit `ELSE null` to get the same result. – 200_success May 12 '16 at 18:26
@200_success: +1 Correct! Changed the answer accordingly. – Daniel May 14 '16 at 07:18
2

You can also use COUNT() to do more esoteric applications like `COUNT(DISTINCT CASE WHEN mycol2 THEN mycol1 END)` which counts distinct mycol1 when mycol2 is true – raphael Apr 11 '17 at 20:29
if you don't put "else 0" and there is no values matching that criteria, you get null from count – Matiaan Mar 15 '22 at 08:28

Ilja Everilä · Answer 2 · 2017-12-27T11:29:18.433

Since PostgreSQL 9.4 there's the FILTER clause, which allows for a very concise query to count the true values:

select count(*) filter (where myCol)
from tbl;

The above query is a bad example in that a simple WHERE clause would suffice, and is for demonstrating the syntax only. Where the FILTER clause shines is that it is easy to combine with other aggregates:

select count(*), -- all
       count(myCol), -- non null
       count(*) filter (where myCol) -- true
from tbl;

The clause is especially handy for aggregates on a column that uses another column as the predicate, while allowing to fetch differently filtered aggregates in a single query:

select count(*),
       sum(otherCol) filter (where myCol)
from tbl;

score 116 · Answer 3 · answered Jan 03 '14 at 18:08

116

Cast the Boolean to an integer and sum.

SELECT count(*),sum(myCol::int);

You get 6,3.

answered Jan 03 '14 at 18:08

Dwayne Towell

8,154
4
36
49

5

Plus1: Nice hack! This is probably even faster than my solution. – Daniel Jun 27 '16 at 11:29
1

This is the best and shortest solution (and has equivalences in many other programming environments and software). Should be up-voted more – Apr 27 '18 at 16:20
5

The 'cast to int and count' is clearly the most concise, but that doesn't make it best. I would not endorse this, because while many environments use the 0/1 representation for false/true, many use 0/non-zero, including -1. I agree it's a "hack," and casts are dicey enough when they aren't "hacks." Won't downvote but again, wouldn't endorse. – Andrew Wolfe May 26 '20 at 21:47

wrobell · Answer 4 · 2015-07-24T14:40:53.603

48

probably, the best approach is to use nullif function.

in general

select
    count(nullif(myCol = false, true)),  -- count true values
    count(nullif(myCol = true, true)),   -- count false values
    count(myCol);

or in short

select
    count(nullif(myCol, true)),  -- count false values
    count(nullif(myCol, false)), -- count true values
    count(myCol);

http://www.postgresql.org/docs/9.0/static/functions-conditional.html

edited Jul 24 '15 at 14:40

answered Aug 31 '11 at 14:22

wrobell

668
5
5

2

Your "in general" looks wrong: AFAICS, `nullif([boolean expression], true)` will return `false` if [boolean expression] is false, and `null` if it is true, so you will be counting the false values. I think you want `nullif([boolean expression], false)`. – rjmunro Jul 22 '15 at 11:21
yep, the "general" case should be the other way around. fixed. thanks. – wrobell Jul 24 '15 at 14:41
1

Yuk. That fix is really confusing. AFAICS, it will now count true or null values. I think that rephrasing it so that you always have `nullif([boolean expression], false)` makes it much easier to read. You can then vary the boolean expression part to be whatever you like, in this case `myCol = true` to count true values, or `myCol = false` to count false values, or `name='john'` to count people called john etc. – rjmunro Jul 24 '15 at 15:09

Le Droid · Answer 5 · 2015-09-23T21:45:57.287

28

The shortest and laziest (without casting) solution would be to use the formula:

SELECT COUNT(myCol OR NULL) FROM myTable;

Try it yourself:

SELECT COUNT(x < 7 OR NULL)
   FROM GENERATE_SERIES(0,10) t(x);

gives the same result than

SELECT SUM(CASE WHEN x < 7 THEN 1 ELSE 0 END)
   FROM GENERATE_SERIES(0,10) t(x);

edited Sep 23 '15 at 21:45

answered Sep 23 '15 at 19:59

Le Droid

4,534
3
37
32

This is definitely a nicer solution than mine :) – Daniel Nov 30 '15 at 23:21
Very insightful answer. – lucasarruda Mar 15 '19 at 19:34

score 10 · Answer 6 · answered Dec 24 '17 at 20:18

10

Simply convert boolean field to integer and do a sum. This will work on postgresql :

select sum(myCol::int) from <table name>

Hope that helps!

answered Dec 24 '17 at 20:18

Jaspreet Singh

131
1
5

It is neither faster nor more precise than the other solutions. I believe you come from Oracle when using ints as boolean is more intuitive for you. – Daniel Jan 20 '20 at 21:51
While it won't work for NULL, its perfect if it is boolean and NOT NULL - was very useful for my case, thanks! – Eugene Mar 21 '23 at 15:30

Kuberchaun · Answer 7 · 2011-03-22T21:09:41.997

9

select f1,
       CASE WHEN f1 = 't' THEN COUNT(*) 
            WHEN f1 = 'f' THEN COUNT(*) 
            END AS counts,
       (SELECT COUNT(*) FROM mytable) AS total_counts
from mytable
group by f1

Or Maybe this

SELECT SUM(CASE WHEN f1 = 't' THEN 1 END) AS t,
       SUM(CASE WHEN f1 = 'f' THEN 1 END) AS f,
       SUM(CASE WHEN f1 NOT IN ('t','f') OR f1 IS NULL THEN 1 END) AS others,
       SUM(CASE WHEN f1 IS NOT NULL OR f1 IS NULL THEN 1 ELSE 0 END) AS total_count
FROM mytable;

edited Mar 22 '11 at 21:09

answered Mar 22 '11 at 19:09

Kuberchaun

29,160
7
51
59

+1 If `myCol` expression is a boolean, you can replace the check with `where (myCol)` – ypercubeᵀᴹ Mar 22 '11 at 19:14
sorry I oversimplified my example: I can't use a where clause as I also want to return a total count representing the total number of rows, as well as a count of the true values. – EoghanM Mar 22 '11 at 20:20

ypercubeᵀᴹ · Answer 8 · 2011-03-22T21:05:42.890

8

In MySQL, you can do this as well:

SELECT count(*) AS total
     , sum(myCol) AS countTrue --yes, you can add TRUEs as TRUE=1 and FALSE=0 !!
FROM yourTable
;

I think that in Postgres, this works:

SELECT count(*) AS total
     , sum(myCol::int) AS countTrue --convert Boolean to Integer
FROM yourTable
;

or better (to avoid :: and use standard SQL syntax):

SELECT count(*) AS total
     , sum(CAST(myCol AS int)) AS countTrue --convert Boolean to Integer
FROM yourTable
;

edited Mar 22 '11 at 21:05

answered Mar 22 '11 at 20:49

ypercubeᵀᴹ

113,259
19
174
235

This is the most simple solution I have ever seen ^_^ – JiaHao Xu Mar 28 '20 at 00:33

vol7ron · Answer 9 · 2011-03-22T19:25:14.923

4

SELECT count(*)         -- or count(myCol)
FROM   <table name>     -- replace <table name> with your table
WHERE  myCol = true;

Here's a way with Windowing Function:

SELECT DISTINCT *, count(*) over(partition by myCol)
FROM   <table name>;

-- Outputs:
-- --------------
-- myCol | count
-- ------+-------
--  f    |  2
--  t    |  3
--       |  1

edited Mar 22 '11 at 19:25

answered Mar 22 '11 at 19:13

vol7ron

40,809
21
119
172

sorry I can't return multiple rows for the more complicated example I'm applying this solution to. – EoghanM Mar 22 '11 at 20:33
Yes, but you can restrict it further by just adding `WHERE myCol = true`. I provided the second example not because it's any faster, but more as an educational piece to Postgres's windowing functions, which many users aren't comfortable with, or don't know about. – vol7ron Mar 23 '11 at 00:00

score 4 · Answer 10 · answered Jul 13 '21 at 15:39

Benchmark

TL;DR: take the solution you like. There's no significant difference.

Utility scripts

before(){
    psql <<-SQL
        create table bench (
                id         serial
            , thebool    boolean
        );

        insert into bench (thebool)
        select (random() > 0.5)
        from generate_series(1, 1e6) g;


        analyze bench;
    SQL
}
after(){
    psql -c 'drop table bench'
}
test(){
    echo $(tput bold)$1$(tput sgr0)
    psql -c "explain analyze select $1 from bench" | tail -4 | head -2
}

Actual benchmark

Made on a 1.4GHz i5 MacBookPro, psql and pg 12.4 (pg in a linux docker container):

before  
test 'count(*) filter (where thebool)'
# Planning Time: 0.138 ms
# Execution Time: 4424.042 ms
test 'count(case when thebool then 1 end)'
# Planning Time: 0.156 ms
# Execution Time: 4638.861 ms
test 'count(nullif(thebool, false))'
# Planning Time: 0.201 ms
# Execution Time: 5267.631 ms
test 'count(thebool or null)'
# Planning Time: 0.202 ms
# Execution Time: 4672.700 ms
test 'sum(thebool::integer)'
# Planning Time: 0.155 ms
# Execution Time: 4602.406 ms
test 'coalesce(sum(case when thebool THEN 1 ELSE 0 END), 0)'
# Planning Time: 0.167 ms
# Execution Time: 4416.503 ms
after

score 0 · Answer 11 · answered Jun 26 '20 at 11:41

0

select count(myCol)
from mytable
group by myCol
;

will group the 3 possible states of bool (false, true, 0) in three rows especially handy when grouping together with another column like day

answered Jun 26 '20 at 11:41

hazard5000

1
2

postgresql - sql - count of `true` values

11 Answers11

Benchmark

Utility scripts

Actual benchmark

Linked

Related