postgresql - sql - count of `true` values

Question

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>

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;

Cast the Boolean to an integer and sum.

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

You get 6,3.

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

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);