基于 SQL Server 中的条件进行计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3455201/
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
Count based on condition in SQL Server
提问by Jin Yong
Does anyone know how can I do a count in SQL Server based on condition.
有谁知道我如何根据条件在 SQL Server 中进行计数。
Example:
例子：
How can I do a column count for records with name 'system', and total CaseID records in the table?
如何对名称为“system”的记录进行列计数，以及表中的总 CaseID 记录？
Customer table
客户表
UserID CaseID Name
1 100 alan
1 101 alan
1 102 amy
1 103 system
1 104 ken
1 105 ken
1 106 system
The result will display like below:
结果将显示如下：
UserID TotalCaseID TotalRecordsWithSystem
1 7 2
回答by gbn
Use SUM/CASE...
使用 SUM/CASE...
SELECT
COUNT(*), --total
SUM(CASE WHEN name = 'system' THEN 1 ELSE 0 END) --conditional
FROM
myTable
回答by Matt
I think he wanted user id in the results
我认为他想要结果中的用户 ID
SELECT
userid,
COUNT(*) as TotalcaseID, --total
SUM(CASE WHEN name = 'system' THEN 1 ELSE 0 END) as TotalRecordsWithSystem
FROM
myTable
group by userid
回答by kage
select
userid,
count('x') as TotalCaseID,
count(case when name = 'system' then 'x' else null end) as TotalRecordsWithSystem
from CustomerTable
group by userid
