A simple example from Postgres offical documents:
SELECT * FROM test;
CASE WHEN a=1 THEN 'one'
WHEN a=2 THEN 'two'
a | case
1 | one
2 | two
3 | other
It is based on this form:
CASE WHEN condition THEN result
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