Sql: Count Distinct Values From One Column Based On Multiple Criteria In Other Columns

I am trying to do count distinct values based on multiple criteria. Sample data exercise included below. Table1 ╔════════╦════════�

Solution 1:

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

SELECT Test, COUNT(DISTINCT "Bug ID") AS "Total Bugs",
count(distinct (CASEWHEN "Status" <>'Closed'THEN "Bug ID" END)) as "Open Bugs"
FROM Table1
GROUPBY 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.