CODEWITHSUNDEEP
sql-server
shanlodh
shanlodh

Reputation: 1045

SQL Server: Count distinct occurrences in one field by value in another

Currently I'm writing two queries to count distinct occurrences of fieldOne for each possible value of fieldTwo. How can I do this in one query? Thanks 

select 
    count(*) from(select distinct(fieldOne) from myTable where fieldTwo= 'valueOne')x 


select 
    count(*) from(select distinct(fieldOne) from myTable where fieldTwo = 'valueTwo') y

Upvotes: 0

Views: 37

Answers (2)

Richard
Richard

Reputation: 108995

This can be done with cross apply to remove the need to know the possible values in fieldTwo:

select twos.FieldTwo, count(1)
from (select distinct fieldTwo from MyTable) twos
  cross apply (select distinct t.fieldOne
               from MyTable t
               where t.fieldTwo = twos.FieldTwo) ones
group by twos.FieldTwo

Upvotes: 0

Chanukya
Chanukya

Reputation: 5893

Try using CASE statement 

SELECT  COUNT(DISTINCT CASE WHEN  FIELDTWO= 'VALUEONE' THEN FIELDONE END) X ,
COUNT(DISTINCT CASE WHEN  FIELDTWO= 'VALUETWO' THEN FIELDONE END)Y
FROM MYTABLE

Upvotes: 1

Related Questions