sql - How can I join 2 different counts of the same table together? -

the table i'm working on records of answers amount of questions. want find out percentage of people said 'yes, definitely' provider 23 in question 470,477,479,481,483. tried count total number of answers , 'yes, definitely' answers. can them separately fail join them in 1 table.

one query total counts of records in table surveyanswerfact.

select surveyquestionkey, count(1) [warehouse].[dbo].[surveyanswerfact] ([providerkey] = 23)  , ([surveyquestionkey] in (470,477,479,481,483))         , ([response] = 'yes, definitely')  , ([responsedatekey] between 20140601 , 20150531) group surveyquestionkey 

results:

470   44 477   40 479   43 481   43 483   44 

another query counts of answers 'yes, definitely'

select surveyquestionkey, count(1) [warehouse].[dbo].[surveyanswerfact] (providerkey = 23)  , (surveyquestionkey in (470,477,479,481,483))  , ([responsedatekey] between 20140601 , 20150531) group surveyquestionkey 

results:

470   43 477   39 479   35 481   42 483   39 

i tried join them using sql, results same first query's results. expected result this:

470   43   44 477   39   40 479   35   43 481   42   43 483   39   44 

can teach me correct way join them?

maybe mean...

all did eliminate clause on response , added select , group by.

 select surveyquestionkey, response, count(1)     [warehouse].[dbo].[surveyanswerfact]     ([providerkey] = 23)        , ([surveyquestionkey] in (470,477,479,481,483))               , ([responsedatekey] between 20140601 , 20150531)    group surveyquestionkey, response 

--------------update based on new info---------

there's few ways this. use case statement

select surveyquestionkey, count(1) totalcount, sum(case when response = 'yes, definitely' 1 else 0 end) [yes def] [warehouse].[dbo].[surveyanswerfact] ([providerkey] = 23)  , ([surveyquestionkey] in (470,477,479,481,483))         , ([responsedatekey] between 20140601 , 20150531) group surveyquestionkey 

another way join tables suggest has more overhead. (this uses common table each query make join easier read.)

with totalcount (select surveyquestionkey, count(1) cnt [warehouse].[dbo].[surveyanswerfact] ([providerkey] = 23)  , ([surveyquestionkey] in (470,477,479,481,483))         , ([response] = 'yes, definitely')  , ([responsedatekey] between 20140601 , 20150531) group surveyquestionkey),  yesdefcount (select surveyquestionkey, count(1) cnt [warehouse].[dbo].[surveyanswerfact] ([providerkey] = 23)  , ([surveyquestionkey] in (470,477,479,481,483))         , ([responsedatekey] between 20140601 , 20150531) group surveyquestionkey)  select surveyquestionkey, totalcount.cnt, yesdefcount.count totalcount left join yesdefcount b   on a.surveyquestionkey = b.surveyquestionkey;