Today I Learned

hashrocket A Hashrocket project

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;
See More #sql TILs
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, our series of free PostgreSQL screencasts.