sqlpostgresql

postgresql - sql - count of `true` values

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

Solution 
  • 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>