SQL Server 如何使用CASE和ELSE进行COUNT？

sqxo8psd 于 2023-01-25 发布在其他

关注(0)|答案(2)|浏览(147)

目前我有以下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放在哪里呢？
样本数据：
| 身份证|管柱|
| - ------|- ------|
| 1个|x|
| 第二章|x|
| 三个|Y型|
| 四个|项目a|
| 五个|b.人口基金|
| 六个|b.人口基金|
| 七|（c）秘书长的报告|
预期结果：
| 计数_x|计数_y|计数_其他|
| - ------|- ------|- ------|
| 第二章|1个|四个|

sql-server

来源：https://stackoverflow.com/questions/75200171/how-to-do-count-with-case-and-else

2条答案

按热度按时间

xcitsw881#

您可以使用以下逻辑：

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;

赞(0）回复(0）举报 2023-01-25

0mkxixxg2#

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;

赞(0）回复(0）举报 2023-01-25

我来回答

SQL Server 如何使用CASE和ELSE进行COUNT？

2条答案

相关问题

热门标签

最新问答