PostgreSQL Conditional Expression

A simple example from Postgres offical documents:

SELECT * FROM test;

 a
---
 1
 2
 3


SELECT a,
       CASE WHEN a=1 THEN 'one'
	    WHEN a=2 THEN 'two'
	    ELSE 'other'
       END
    FROM test;

 a | case
---|-------
 1 | one
 2 | two
 3 | other

It is based on this form:

CASE WHEN condition THEN result
     [WHEN ...]
     [ELSE result]
END

It is very convenient in aggregation functions, just think table foo below:

id is table’s primary key, sex is 0 or 1, it stands for male and female.

Now, how can we lookup how many males and females at the same time ?

SELECT sum(CASE WHEN sex=0 THEN 1 ELSE 0 END) AS total_male,
       sum(CASE WHEN sex=1 THEN 1 ELSE 0 END) AS total_female
FROM foo;

That’s it.