相当于Sql Server的COUNTIF聚合函数

I'm building a query with a GROUP BY clause 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()行显然会失败，因为不存在称为COUNTIF的本地SQL函数，但是这里的想法是确定具有MyColumn值“1”的所有行的百分比。

Any thoughts on how to properly implement this in a MS SQL 2005 environment?

关于如何在MS SQL 2005环境中正确地实现这一点，您有什么想法吗?

8 个解决方案

#1

251

You could use a SUM (not COUNT!) combined with a CASE statement, 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:

注意:在我自己的测试中，NULLs不是问题，尽管这可能与环境有关。您可以处理诸如:

SELECT SUM(CASE WHEN ISNULL(myColumn,0)=1 THEN 1 ELSE 0 END)
FROM AD_CurrentView

#2

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)没有COUNT NULLs这一事实，使用如下内容:

SELECT COUNT(NULLIF(0, myColumn))
FROM AD_CurrentView

NULLIF - returns NULL if the two passed in values are the same.

如果两个值传递的值相同，则返回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).

优点:表示您希望计数行，而不是使用SUM()符号。缺点:不清楚它是如何工作的(“魔法”通常是不好的)。

#3

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

#4

Adding on to Josh's answer,

补充Josh的回答，

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中)工作得很好，不需要将“count”更改为“sum”，同样的逻辑也可以移植到其他“有条件的集合”上。例:根据一个条件进行总结:

SELECT SUM(CASE WHEN myColumn=1 THEN AD_CurrentView.NumberColumn ELSE 0 END)
FROM AD_CurrentView

#5

Not product-specific, but the SQL standard provides

不是特定于产品的，而是SQL标准提供的。

SELECT COUNT() FILTER WHERE <condition-1>, COUNT() FILTER WHERE <condition-2>, ... FROM ...

选择COUNT()过滤器，其中， COUNT()过滤器，其中，…从…

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.

当然，供应商更愿意坚持他们的专有解决方案。

#6

Why not like this?

为什么不这样呢?

SELECT count(1)
FROM AD_CurrentView
WHERE myColumn=1

#7

How about

如何

SELECT id, COUNT(IF status=42 THEN 1 ENDIF) AS cnt
FROM table
GROUP BY table

Shorter than CASE :)

比案例:短)

Works because COUNT() doesn't count null values, and IF/CASE return null when condition is not met and there is no ELSE.

因为COUNT()不计算空值，当条件不满足且没有其他条件时，IF/CASE返回null。

I think it's better than using SUM().

我认为它比用SUM()好。

#8

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.

当然，您需要根据显示需求格式化结果。

#1

251