Count true values with postgres
I can try to count everything that is not null in postgres:
select count(x.x)
from (
values (null), ('hi'), (null), ('there')
) x (x);
# 2
But if I try to count everything that is true in postgres, I don't get what I want:
select count(x.x)
from (
values (false), (true), (false), (true)
) x (x);
# 4
I can, however, take advantage of the postgres ability to cast boolean to int:
select true::int;
# 1
select false::int;
# 0
Using ::int
I get:
select count(x.x::int)
from (
values (false), (true), (false), (true)
) x (x);
# 4
Postgres is still counting everything that is not null, but what if use sum
instead?
select sum(x.x::int)
from (
values (false), (true), (false), (true)
) x (x);
# 2
Because everything is either a 0 or a 1, sum
behaves like count
.
Now you can do something like this:
select
sum((status = 'Awesome')::int) as awesomes,
sum((status = 'Terrible')::int) as terribles
from statuses;
Tweet