如何用CASE和ELSE计数?

Question

目前我有以下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
...

我想修改它，让它继续计数x和y但如果不是x或y，则计数为count_other

我该怎么做呢?在这种情况下，我把ELSE放在哪里?

样本数据:

<表类>id列tbody><<tr>1x2x3y45b6b7c

您可以使用以下逻辑:

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;

Select X count_x,Y count_y,count_other 
From(Select Case when Col not in ('X','Y') Then 'count_other' else col End Col From #TableNmae)T pivot (Count (Col) For col in (X,Y,count_other)) pvt;
Select col,Count() from #TableNmae Where col in ('X','Y') Group by col Union Select 'Othercolumns' col,Count() from #TableNmae Where col not in ('X','Y');
Select col ,Count(*) From(Select Case when col not in ('X','Y') then 'Othercolumns' else col end col from #TableNmae )T Group by col;
Select (Select COUNT() From #TableNmae Where col ='X') count_x, (Select COUNT() From #TableNmae Where col ='Y') count_y, (Select COUNT(*) From #TableNmae Where col not in ('X','Y')) count_other;