title: Window functions to count distinct records
body: <p>The query below is based on a complicated view and the view works as I want it to (I'm not going to include the view because I don't think it will help with the question at hand).  What I can't get right is the <code>drugCountsinFamilies</code> column.  I need it to show me the number of <code>distinct drugName</code>s for each drug family.  You can see from the first screencap that there are three different H3A rows.  The <code>drugCountsInFamilies</code> for H3A should be 3 (there are three different  H3A drugs.  )</p>
  
  <p><img src="https://i.stack.imgur.com/jeRjX.jpg" alt="enter image description here"></p>
  
  <p>You can see from the second screen cap that what's happening is the <code>drugCountsInFamilies</code> in the first screen cap is catching the number of rows that the drug name is listed on.<br>
  <img src="https://i.stack.imgur.com/7pzQi.jpg" alt="enter image description here"></p>
  
  <p>Below is my question, with comments on the part that is incorrect</p>
  
  <pre><code>select distinct
       rx.patid
      ,d2.fillDate
      ,d2.scriptEndDate
      ,rx.drugName
      ,rx.drugClass
      --the line directly below is the one that I can't figure out why it's wrong
      ,COUNT(rx.drugClass) over(partition by rx.patid,rx.drugclass,rx.drugname) as drugCountsInFamilies
  from 
  (
  select 
      ROW_NUMBER() over(partition by d.patid order by d.patid,d.uniquedrugsintimeframe desc) as rn
      ,d.patid
      ,d.fillDate
      ,d.scriptEndDate
      ,d.uniqueDrugsInTimeFrame
      from DrugsPerTimeFrame as d
  )d2
  inner join rx on rx.patid = d2.patid
  inner join DrugTable as dt on dt.drugClass=rx.drugClass
  where d2.rn=1 and rx.fillDate between d2.fillDate and d2.scriptEndDate
  and dt.drugClass in ('h3a','h6h','h4b','h2f','h2s','j7c','h2e')
  order by rx.patid
  </code></pre>
  
  <p>SSMS gets mad if I try to add a distinct to the <code>count(rx.drugClass)</code> clause.  Can it be done using window functions?</p>