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

Question

Hello all,

First of all, I don't want to use a "join" because that will make my query longer and difficult to read. So what I need to do must be withing the same SELECT statement.

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

H columnd tells if a record is 'Open' or 'Close', here how 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 a result: COUNT(DISTINCT ID) WHERE H = 'Open' so that I can get numberOfOpenRecords.

I can't write my new condition to my "WHERE", because that will effect the results, like numberOfRecords.

Hope I explained my problem.

Thanks for helps.

Answer 1

Since count doesn't count null values, you could:

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