Window functions to count distinct records

sql, sql-server-2008, t-sql

Solution

Doing a `count(distinct)` as a windows function requires a trick. Several levels of tricks, actually.

Because your request is actually truly simple -- the value is always 1 because rx.drugClass is in the partitioning clause -- I will make an assumption. Let's say you want to count the number of unique drug classes per patid.

If so, do a `row_number()` partitioned by patid and drugClass. When this is 1, within a patid, , then a new drugClass is starting. Create a flag that is 1 in this case and 0 in all other cases.

Then, you can simply do a `sum` with a partitioning clause to get the number of distinct values.

The query (after formatting it so I can read it), looks like:

select rx.patid, d2.fillDate, d2.scriptEndDate, rx.drugName, rx.drugClass,
       SUM(IsFirstRowInGroup) over (partition by rx.patid) as NumDrugCount
from (select distinct rx.patid, d2.fillDate, d2.scriptEndDate, rx.drugName, rx.drugClass,
             (case when 1 = ROW_NUMBER() over (partition by rx.drugClass, rx.patid order by (select NULL))
                   then 1 else 0
              end) as IsFirstRowInGroup
      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 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')
     ) t
order by patid

Problem

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 `drugCountsinFamilies` column. I need it to show me the number of `distinct drugName`s for each drug family. You can see from the first screencap that there are three different H3A rows. The `drugCountsInFamilies` for H3A should be 3 (there are three different H3A drugs. ) You can see from the second screen cap that what's happening is the `drugCountsInFamilies` in the first screen cap is catching the number of rows that the drug name is listed on. Below is my question, with comments on the part that is incorrect ``` 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 ``` SSMS gets mad if I try to add a distinct to the `count(rx.drugClass)` clause. Can it be done using window functions?

Original source