sql server - SQL: Count distinct values from one column based on multiple criteria in other columns

Question

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.

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