相当于 COUNTIF 聚合函数的 Sql Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/582637/
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
Sql Server equivalent of a COUNTIF aggregate function
提问by senfo
I'm building a query with a GROUP BYclause that needs the ability to count records based only on a certain condition (e.g. count only records where a certain column value is equal to 1).
我正在使用一个GROUP BY子句构建一个查询，该子句需要能够仅根据特定条件对记录进行计数（例如，仅对某个列值等于 1 的记录进行计数）。
SELECT UID,
COUNT(UID) AS TotalRecords,
SUM(ContractDollars) AS ContractDollars,
(COUNTIF(MyColumn, 1) / COUNT(UID) * 100) -- Get the average of all records that are 1
FROM dbo.AD_CurrentView
GROUP BY UID
HAVING SUM(ContractDollars) >= 500000
The COUNTIF()line obviously fails since there is no native SQL function called COUNTIF, but the idea here is to determine the percentage of all rows that have the value '1' for MyColumn.
该COUNTIF()行显然失败了，因为没有调用本机 SQL 函数COUNTIF，但这里的想法是确定 MyColumn 的值为“1”的所有行的百分比。
Any thoughts on how to properly implement this in a MS SQL 2005 environment?
关于如何在 MS SQL 2005 环境中正确实现这一点的任何想法？
回答by JoshBerke
You could use a SUM(not COUNT!) combined with a CASEstatement, like this:
您可以将SUM（不是COUNT！）与CASE语句结合使用，如下所示：
SELECT SUM(CASE WHEN myColumn=1 THEN 1 ELSE 0 END)
FROM AD_CurrentView
Note: in my own test NULLs were not an issue, though this can be environment dependent. You could handle nulls such as:
注意：在我自己的测试NULL中，这不是问题，尽管这可能取决于环境。您可以处理空值，例如：
SELECT SUM(CASE WHEN ISNULL(myColumn,0)=1 THEN 1 ELSE 0 END)
FROM AD_CurrentView
回答by Chris Shaffer
I usually do what Josh recommended, but brainstormed and tested a slightly hokey alternative that I felt like sharing.
我通常会按照 Josh 的建议进行操作，但集思广益并测试了一个我想分享的有点古怪的替代方案。
You can take advantage of the fact that COUNT(ColumnName) doesn't count NULLs, and use something like this:
您可以利用 COUNT(ColumnName) 不计算 NULL 的事实，并使用以下内容：
SELECT COUNT(NULLIF(0, myColumn))
FROM AD_CurrentView
NULLIF - returns NULL if the two passed in values are the same.
NULLIF - 如果传入的两个值相同，则返回 NULL。
Advantage: Expresses your intent to COUNT rows instead of having the SUM() notation. Disadvantage: Not as clear how it is working ("magic" is usually bad).
优点：表达您对 COUNT 行而不是 SUM() 表示法的意图。缺点：不太清楚它是如何工作的（“魔法”通常是坏的）。
回答by asgeo1
I would use this syntax. It achives the same as Josh and Chris's suggestions, but with the advantage it is ANSI complient and not tied to a particular database vendor.
我会使用这种语法。它实现了与 Josh 和 Chris 的建议相同的功能，但优点是它符合 ANSI 标准并且不依赖于特定的数据库供应商。
select count(case when myColumn = 1 then 1 else null end)
from AD_CurrentView
回答by Sturgus
Adding on to Josh's answer,
加上乔希的回答，
SELECT COUNT(CASE WHEN myColumn=1 THEN AD_CurrentView.PrimaryKeyColumn ELSE NULL END)
FROM AD_CurrentView
Worked well for me (in SQL Server 2012) without changing the 'count' to a 'sum' and the same logic is portable to other 'conditional aggregates'. E.g., summing based on a condition:
对我来说效果很好（在 SQL Server 2012 中），无需将“计数”更改为“总和”，并且相同的逻辑可移植到其他“条件聚合”。例如，基于条件求和：
SELECT SUM(CASE WHEN myColumn=1 THEN AD_CurrentView.NumberColumn ELSE 0 END)
FROM AD_CurrentView
回答by maf-soft
How about
怎么样
SELECT id, COUNT(IF status=42 THEN 1 ENDIF) AS cnt
FROM table
GROUP BY table
Shorter than CASE:)
短于CASE:)
Works because COUNT()doesn't count null values, and IF/CASEreturn null when condition is not met and there is no ELSE.
有效是因为COUNT()不计算空值，并且IF/CASE在不满足条件且没有ELSE.
I think it's better than using SUM().
我认为这比使用SUM().
回答by Fandango68
I had to use COUNTIF() in my case as part of my SELECT columns AND to mimic a % of the number of times each item appeared in my results.
在我的案例中，我不得不使用 COUNTIF() 作为我的 SELECT 列的一部分，并模拟每个项目出现在我的结果中的次数的百分比。
So I used this...
所以我用了这个...
SELECT COL1, COL2, ... ETC
(1 / SELECT a.vcount
FROM (SELECT vm2.visit_id, count(*) AS vcount
FROM dbo.visitmanifests AS vm2
WHERE vm2.inactive = 0 AND vm2.visit_id = vm.Visit_ID
GROUP BY vm2.visit_id) AS a)) AS [No of Visits],
COL xyz
FROM etc etc
Of course you will need to format the result according to your display requirements.
当然，您需要根据您的显示要求格式化结果。
回答by Erwin Smout
Not product-specific, but the SQL standard provides
不是特定于产品的，但 SQL 标准提供了
SELECT COUNT() FILTER WHERE <condition-1>,
COUNT() FILTER WHERE <condition-2>, ...
FROM ...
SELECT COUNT() FILTER WHERE <condition-1>,
COUNT() FILTER WHERE <condition-2>, ...
FROM ...
for this purpose. Or something that closely resembles it, I don't know off the top of my hat.
以此目的。或者与它非常相似的东西，我不知道。
And of course vendors will prefer to stick with their proprietary solutions.
当然，供应商更愿意坚持使用他们的专有解决方案。
回答by Michal
Why not like this?
为什么不喜欢这个？
SELECT count(1)
FROM AD_CurrentView
WHERE myColumn=1
回答by Andres Sarria
SELECT COALESCE(IF(myColumn = 1,COUNT(DISTINCT NumberColumn),NULL),0) column1,
COALESCE(CASE WHEN myColumn = 1 THEN COUNT(DISTINCT NumberColumn) ELSE NULL END,0) AS column2
FROM AD_CurrentView
