Yesterday on IRC Andrew Gierth suggested it would be good to have a count_if aggregate that would count the rows where it's argument is true. This seems so obviously useful that I cooked up a version quickly in two tiny bits of SQL:
andrew=# create or replace function countif_trans(bigint, bool)
andrew-# returns bigint language sql as
andrew-# $$ select case when $2 then $1 + 1 else $1 end $$;
andrew=# create aggregate count_if (bool)
andrew-# (sfunc = countif_trans,
andrew(# stype = bigint, initcond = 0);
Now you can easily do repeated counts of different conditions in one pass over a set of rows:
andrew=# select count_if(relkind in ('r','v','S'))as "r,v,S",
andrew-# count_if(relkind in ('i', 't')) as "i,t"
andrew-# from pg_class;
r,v,S | i,t
165 | 134
For speed the transition function should probably be written in C, but for many purposes this is good enough.
Yes you can do it, but I personally hate casting a bool to an int - it's breaking the boolean abstraction (and I've been told that such a cast is contrary to the Standard). So I think this is at least much more expressive.
I usually just do this with conditionals inside my aggregates:<br />
select sum(case when relkind in ('r','v','S') then 1 else 0 end) as "r,v,S",<br />
sum(case when relkind in ('i', 't') then 1 else 0 end) as "i,t"<br />
The SQL standard actually has a syntax for that:<br />
count(*) FILTER (WHERE relkind IN ('r','v','S'))<br />
I've been meaning to work on implementing that to postgres for over a year now, but my laziness always seems to get the best of me.