CODEWITHSUNDEEP
sql
jn1982
jn1982

Reputation: 13

Microsoft SQL with more than one distinct COUNT and WHERE clause

I have a data in Microsoft SQL server with the following format:

id1 id2 month   quantA  quantB  
1   10  1   5   15  
1   10  1   10  20  
1   10  2   5   10  
1   10  2   10  NULL  
1   11  1   NULL    NULL  
1   11  2   5   NULL  
1   11  2   10  5  
2   10  1   10  20  
2   10  1   5   NULL  
2   11  2   NULL    NULL

I need to construct a table grouped by id1 and month with the following columns:

id1  
month  
var1 = count how many *distinct* id2 by month and id1 for which quantA!=Null  
var2 = count how many *distinct* id2 by month and id1 for which quantB!=Null

Upvotes: 1

Views: 251

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can construct the query basically how you have written it:

select id1, month,
       count(distinct case when quantA is not null then id2 end) as var1,
       count(distinct case when quantB is not null then id2 end) as var2
from t
group by id1, month

COUNT DISTINCT ignores NULLs when doing the count.

Upvotes: 2

Related Questions