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

我正在尝试根据多个条件计算不同的值.下面包含示例数据练习.

<前>表格1╔========╦=======╦======╗║ 错误 ID ║ 状态 ║ 测试 ║╠========╬=======╬======╣║ 1 ║ 打开 ║ w ║║ 2 ║ 关闭 ║ w ║║ 3 ║ 打开 ║ w ║║ 4 ║ 打开 ║ x ║║ 4 ║ 打开 ║ x ║║ 5 ║ 关闭 ║ x ║║ 5 ║ 关闭 ║ x ║║ 5 ║ 关闭 ║ y ║║ 6 ║ 打开 ║ z ║║ 6 ║ 打开 ║ z ║║ 6 ║ 打开 ║ z ║║ 7 ║ 关闭 ║ z ║║ 8 ║ 关闭 ║ z ║╚========╩=======╩======╝所需查询结果╔======╦==========╦===========╗║ 测试 ║ 未解决的错误 ║ 错误总数 ║╠======╬==========╬===========╣║ w ║ 2 ║ 3 ║║ x ║ 1 ║ 2 ║║ y ║ 0 ║ 1 ║║ z ║ 1 ║ 3 ║╚======╩==========╩===========╝

一个给定的 Bug 可以在多个测试中发现，也可以在同一个测试中多次发现(例如:6)，或者两者都出现(例如:5).

以下查询可以很好地准确提供总错误数"

SELECT测试，COUNT(DISTINCT Bug ID) 作为总 Bug"从表格1按测试分组

我的研究使我对以下查询进行了变体.他们错过了不同的错误，因此返回了未解决的错误"列的错误结果(显示在查询下方)

SELECT测试，SUM(CASE WHEN Status <> 'Closed' THEN 1 ELSE 0 END) 作为开放错误"从表格1按测试分组

<前>╔======╦===========╗║ 测试 ║ 打开错误 ║╠＝＝＝＝＝＝╬＝＝＝＝＝＝＝＝＝＝＝＝╣║ w ║ 2 ║║ x ║ 2 ║║ y ║ 0 ║║ z ║ 3 ║╚======╩===========╝

当然，我的最终结果必须在一个表中提供两个计数列(而不是像我为演示目的所做的那样使用单独的查询).

我不想依赖多个子查询，因为我的现场示例将有两个以上的列，这些列的计数来自同一个表，但条件不同.

我正在使用 SQL Server(不确定版本).

非常感谢任何帮助.

解决方案

您可以使用以下代码来设置条件 count(distinct):

SELECT Test, COUNT(DISTINCT "Bug ID") AS "Total Bugs",count(distinct (CASE WHEN "Status" <> 'Closed' THEN "Bug ID" END)) 为 "Open Bugs"从表 1按测试分组

case 语句检查条件.如果为 true，则返回 Bug ID.如果不存在，则默认为 NULL，因此不会计算 id.

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

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

Any help is greatly appreciated.

解决方案

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.