SQL Server 2000, Get COUNT (DISTINCT ID) with a condition that I cannot write to in my WHERE?

Question

First of all, I do not want to use "join" because it will make my request longer and harder to read. So what I need to do is need to have the same SELECT statement.

My columns in myTable: A, B, C, D, time, ID and H

H columnd reports that the entry is "Open" or "Close", this is what my query looks like.

SELECT A, B, C, D, COUNT(DISTINCT ID) AS numberOfRecords, SUM(time) / COUNT(DISTINCT ID) AS averageTimeOfAllRecords FROM myTable WHERE ISNUMERIC(A)=1 AND A IN (SELECT A FROM myTable2) GROUP BY A,B,C,D 

I need the query above to return another column with the result: COUNT (DISTINCT ID) WHERE H = 'Open' so that I can get numberOfOpenRecords.

I cannot write my new condition for my WHERE, because this will affect the results, for example numberOfRecords.

Hope I explained my problem.

Thanks for the help.

Answer 1

Since count does not take null values ​​into account, you can:

 count(distinct case when H = 'Open' then id else null end)