This entry is more of a note to myself, as any SQL guru will probably read it and think less of me for just now realizing it. My problem was as follows.

I have a log table with login, type, and entry columns. What I wanted was a result set grouped by login with counts for when type was 2 and not 2. In other words: a list of logins, how many times they performed some action, and how many times they performed any other action.

My first attempts at this involved subselects, extra joins, and sacrificing a chicken. Turns out, the solution was almost as elegant as you would think it should be:

SELECT login, count(type=2 or NULL), count(type!=2 or NULL)
FROM log GROUP BY login

The count aggregate takes an expression that counts rows from the result set where the expression is not NULL. That's an important point – false is not null. So doing count(type=2) and count(type!=2) will yield the same results. Adding an "or NULL" to the expression will cause it to short-circuit when true, and evaluate to NULL when false.

Problem solved.