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.