SQL：根据其他列中的多个条件计算一列中的不同值

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'

以下查询可以正常传递'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

我的研究让我对以下查询进行了修改。他们错过了明显的错误,因此返回“Open Bugs”列的错误结果(显示在查询下方)

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.

任何帮助是极大的赞赏。

1 个解决方案

#1

You can have a conditional count(distinct) by using this code:

您可以使用以下代码进行条件计数(不同):

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 case statement checks the condition. When true, it returns the Bug ID. When not present, it defaults to NULL, so the id does not get counted.

案例陈述检查条件。如果为true,则返回Bug ID。如果不存在,则默认为NULL,因此不会计算id。

#1