CODEWITHSUNDEEP
sqlsql-servert-sql
BenThomas
BenThomas

Reputation: 53

How to take count of distinct rows which have a specific column with NULL values is all rows

I have a table CodeResult as follows:

enter image description here

Here we can notice that Code 123 alone has a Code2, that has a value in Result. I want to take a count of distinct Codes that has no values at all in Result. Which means, in this example, I should get 2.

I do not want to use group by clause because it will slow down the query.

Below code gives wrong result: Select count(distinct code) from CodeResult where Result is Null

Upvotes: 0

Views: 54

Answers (3)

HABO
HABO

Reputation: 15816

The following query will return each of the Code values for which there are no corresponding non-NULL values in CodeResult:

select distinct Code
  from CodeResult as CR
  where not exists
    ( select 42 from CodeResult as iCR where iCR.Code = CR.Code and iCR.CodeResult is not NULL );

Counting the rows is left as an exercise for the reader.

Upvotes: 0

marcothesane
marcothesane

Reputation: 6721

You simply can't avoid a GROUP BY: In all DBMSs I know, the query plan you get from a:

SELECT DISTINCT a,b,c FROM tab; ,

is the same as the one for:

SELECT a,b,c FROM tab GROUP BY a,b,c;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269533

One method is two levels of aggregation:

select count(*)
from (select code
      from t
      group by code
      having max(result) is null
     ) c;

A more clever method doesn't use a subquery. It counts the number of distinct codes and then removes the ones that have a result:

select ( count(distinct code) -
         count(distinct case when result is not null then code end )
       )
from t;

Upvotes: 2

Related Questions