相当于 COUNTIF 聚合函数的 Sql Server [英] Sql Server equivalent of a COUNTIF aggregate function

问题描述

我正在使用 GROUP BY 子句构建查询，该子句需要能够仅根据特定条件对记录进行计数(例如，仅对某个列值等于 1 的记录进行计数).

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).

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

COUNTIF() 行显然失败了，因为没有名为 COUNTIF 的本机 SQL 函数，但这里的想法是确定具有该值的所有行的百分比MyColumn 为1".

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.

对如何在 MS SQL 2005 环境中正确实现这一点有任何想法吗?

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

推荐答案

您可以使用 SUM(而不是 COUNT!)与 CASE语句，像这样:

You could use a SUM (not COUNT!) combined with a CASE statement, like this:

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

注意:在我自己的测试中 NULL 不是问题，尽管这可能取决于环境.您可以处理空值，例如:

Note: in my own test NULLs were not an issue, though this can be environment dependent. You could handle nulls such as:

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

这篇关于相当于 COUNTIF 聚合函数的 Sql Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！