How to do COUNT with CASE and ELSE?-CodePudding

Currently I have the following SELECT:

SELECT column,
COUNT(CASE WHEN column='x' THEN 1 END) AS count_x,
COUNT(CASE WHEN column='y' THEN 1 END) AS count_y,
COUNT(CASE WHEN column='a' THEN 1 END) AS count_a,
COUNT(CASE WHEN column='b' THEN 1 END) AS count_b
...

I want to modify it so that it will keep counting x and y but any other case that's not x or y count as count_other

How can I do that? Where do I put the ELSE in this case?

Sample data:

id	column
1	x
2	x
3	y
4	a
5	b
6	b
7	c

Expected result:

count_x	count_y	count_other
2	1	4

CodePudding user response：

You may use the following logic:

SELECT
    col,
    COUNT(CASE WHEN col = 'x' THEN 1 END) AS x,
    COUNT(CASE WHEN col = 'y' THEN 1 END) AS y,
    COUNT(CASE WHEN col = 'a' THEN 1 END) AS a,
    COUNT(CASE WHEN col = 'b' THEN 1 END) AS b,
    COUNT(CASE WHEN col NOT IN ('x', 'y') THEN 1 END) AS count_other
FROM yourTable;