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
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
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.