SQL：根据其他列中的多个条件计算一列中的不同值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16156717/
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: Count distinct values from one column based on multiple criteria in other columns
提问by Blake
I am trying to do count distinct values based on multiple criteria.Sample data exercise included below.
我正在尝试根据多个标准计算不同的值。下面包括示例数据练习。
Table1
╔════════╦════════╦══════╗
║ Bug ID ║ Status ║ Test ║
╠════════╬════════╬══════╣
║ 1 ║ Open ║ w ║
║ 2 ║ Closed ║ w ║
║ 3 ║ Open ║ w ║
║ 4 ║ Open ║ x ║
║ 4 ║ Open ║ x ║
║ 5 ║ Closed ║ x ║
║ 5 ║ Closed ║ x ║
║ 5 ║ Closed ║ y ║
║ 6 ║ Open ║ z ║
║ 6 ║ Open ║ z ║
║ 6 ║ Open ║ z ║
║ 7 ║ Closed ║ z ║
║ 8 ║ Closed ║ z ║
╚════════╩════════╩══════╝
Desired Query Results
╔══════╦═══════════╦════════════╗
║ Test ║ Open Bugs ║ Total Bugs ║
╠══════╬═══════════╬════════════╣
║ w ║ 2 ║ 3 ║
║ x ║ 1 ║ 2 ║
║ y ║ 0 ║ 1 ║
║ z ║ 1 ║ 3 ║
╚══════╩═══════════╩════════════╝
A given Bug can be found in multiple Tests, multiple times for the same Test(ex: 6), or both (ex: 5).
一个给定的 Bug 可以在多个测试中找到，同一个测试多次（例如：6），或两者（例如：5）。
The following query works fine to accurately deliver 'Total Bugs'
以下查询可以很好地准确提供“总错误”
SELECT
Test,
COUNT(DISTINCT Bug ID) AS "Total Bugs"
FROM
Table1
GROUP BY Test
My research has led me to variations on the following query. They miss the distinct bugs and therefore return the incorrect results (shown below the query) for the 'Open Bugs' column
我的研究使我对以下查询进行了更改。他们错过了不同的错误，因此为“打开的错误”列返回了不正确的结果（显示在查询下方）
SELECT
Test,
SUM(CASE WHEN Status <> 'Closed' THEN 1 ELSE 0 END) AS "Open Bugs"
FROM
Table1
GROUP BY Test
╔══════╦═══════════╗ ║ Test ║ Open Bugs ║ ╠══════╬═══════════╣ ║ w ║ 2 ║ ║ x ║ 2 ║ ║ y ║ 0 ║ ║ z ║ 3 ║ ╚══════╩═══════════╝
Of course my end result must deliver both count columns in one table (rather than using separate queries as I have done for demonstration purposes).
当然，我的最终结果必须在一个表中提供两个计数列（而不是像我为演示目的所做的那样使用单独的查询）。
I would like not rely on multiple subqueries because my live example will have more than two columns with counts from the same table but various criteria.
我不想依赖多个子查询，因为我的现场示例将有两个以上的列，这些列的计数来自同一个表，但有不同的条件。
I am working with SQL Server (not sure release).
我正在使用 SQL Server（不确定版本）。
Any help is greatly appreciated.
任何帮助是极大的赞赏。
回答by Gordon Linoff
You can have a conditional count(distinct)by using this code:
您可以count(distinct)使用以下代码来设置条件：
SELECT Test, COUNT(DISTINCT "Bug ID") AS "Total Bugs",
count(distinct (CASE WHEN "Status" <> 'Closed' THEN "Bug ID" END)) as "Open Bugs"
FROM Table1
GROUP BY Test
The casestatement checks the condition. When true, it returns the Bug ID. When not present, it defaults to NULL, so the id does not get counted.
该case语句检查条件。当为真时，它返回Bug ID. 当不存在时，它默认为 NULL，因此 id 不会被计算在内。
