title: Case when in variable declaration
body: <p><img src="https://i.stack.imgur.com/jvpqC.jpg" alt="enter image description here"></p>
  
  <p>I'm looking for another column that will give the overall total counts from each gender next to the <code>eduCounts</code> column.  The rest of the query works fine.  Finding the distinct gender counts in this case has to be done by <code>select count(distinct patid) from...</code></p>
  
  <p>first try:</p>
  
  <pre><code>declare @sexcounts int
  set @sexcounts = case when members.sex 'm' then (select COUNT(distinct patid) from members where sex='m')
                   when members.sex 'f' then (select COUNT(distinct patid) from members where sex='f')
  
  select sex, edutext, COUNT(*) as eduCounts from 
  (
  select distinct m.patid, m.sex, e.eduText
      from members as m
      inner join claims as c on c.patid=m.PATID
      inner join icdClm as ic on ic.clmid=c.clmid
      inner join tblICD as t on t.ICD=ic.icd
      inner join EducationTable as e on e.eduID=m.education
      inner join IncomeTable as i on i.incomeID=m.income
      where ISNUMERIC(ic.icd)=1 and SUBSTRING(ic.icd,1,3)='707'
  )t
  group by sex, eduText
  order by sex
  </code></pre>
  
  <p>second try:</p>
  
  <pre><code>declare @sexcounts int
  set @sexcounts = (select COUNT(distinct patid),case when sex 'm' then (select COUNT(distinct PATID) from members where sex='m') else (select COUNT(distinct patid) from members where sex='f') 
                      from members)
  
      select sex, edutext, COUNT(*) as eduCounts from 
      (
      select distinct m.patid, m.sex, e.eduText
          from members as m
          inner join claims as c on c.patid=m.PATID
          inner join icdClm as ic on ic.clmid=c.clmid
          inner join tblICD as t on t.ICD=ic.icd
          inner join EducationTable as e on e.eduID=m.education
          inner join IncomeTable as i on i.incomeID=m.income
          where ISNUMERIC(ic.icd)=1 and SUBSTRING(ic.icd,1,3)='707'
      )t
      group by sex, eduText
      order by sex
  </code></pre>
  
  <p>I know I could make a derived table and join on the sex column, but I'd like to know how to do it with a variable if at all possible.</p>