SQL Server 2008 中的案例和计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7226646/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Case and Count in SQL Server 2008
提问by Arian
I have a table that stores multiple items for a state and I want to get count for every states according to specific conditions. I wrote this query:
我有一个表，用于存储一个州的多个项目，我想根据特定条件对每个州进行计数。我写了这个查询：
SELECT
State_ID,
State_Name,
State_All= CASE WHEN type1=1 AND type2=1 THEN COUNT(Id) END
State_w= CASE WHEN type1=2 AND type2=1 THEN COUNT(Id) END
State_s= CASE WHEN type1=2 AND type2=2 THEN COUNT(Id) END
FROM
tblStates
but I get this Error:
但我收到此错误：
Column 'State_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
When I added GROUP BYclause For State_ID,I got above error again for State_Name,and when added State_Name to GROUP BYclause ,I got error for State_All,State_w,State_s.
当我GROUP BY为 State_ID添加子句时，State_Name 再次出现上述错误，当将 State_Name 添加到GROUP BY子句时，State_All、State_w、State_s 出现错误。
I don't have a column called State_All,State_w,State_s in my table.
我的表中没有名为 State_All,State_w,State_s 的列。
How I can get count according to specific conditions without using CURSORS?
如何在不使用的情况下根据特定条件获得计数CURSORS？
采纳答案by JT Turner
Would this fix it?
这能解决吗？
SELECT
State_ID,
State_Name,
CASE WHEN type1=1 AND type2=1 THEN COUNT(Id) END AS State_All,
CASE WHEN type1=2 AND type2=1 THEN COUNT(Id) END AS State_w,
CASE WHEN type1=2 AND type2=2 THEN COUNT(Id) END AS State_s
FROM
tblStates
GROUP BY State_ID, State_Name
回答by gbn
You were on the right path.
你走在正确的道路上。
You put the condition inside the COUNT like this. COUNT ignores NULLs (which is the implied ELSE in the CASE) so you only count true matches. You need the GROUP BY too.
您像这样将条件放在 COUNT 中。COUNT 忽略 NULL（这是 CASE 中隐含的 ELSE），因此您只计算真正的匹配项。您也需要 GROUP BY。
Your error comes from the use of type1 and type2 outside of the COUNT
您的错误来自在 COUNT 之外使用 type1 和 type2
SELECT
State_ID,
State_Name,
State_All = COUNT(CASE WHEN type1=1 AND type2=1 THEN 1 END),
State_w = COUNT(CASE WHEN type1=2 AND type2=1 THEN 1 END),
State_s = COUNT(CASE WHEN type1=2 AND type2=2 THEN 1 END)
FROM
tblStates
GROUP BY
State_ID, State_Name
回答by user3064912
You can change Countto SUMbecause each record result 1
您可以将Count更改为SUM因为每个记录结果 1
SELECT
State_ID,
State_Name,
State_All = SUM(CASE WHEN type1=1 AND type2=1 THEN 1 END),
State_w = SUM(CASE WHEN type1=2 AND type2=1 THEN 1 END),
State_s = SUM(CASE WHEN type1=2 AND type2=2 THEN 1 END)
FROM
tblStates
GROUP BY
State_ID, State_Name
回答by Ofer Zelig
You should add both columns in the end of your query:
您应该在查询的末尾添加两列：
GROUP BY State_ID, State_Name
