Count (Distinct ([value)) OVER (Partition by) in SQL Server 2008

Question

I have written this and successfully executed in Oracle

COUNT (DISTINCT APEC.COURSE_CODE) OVER (
                                            PARTITION BY s.REGISTRATION_NUMBER
                                            ,APEC.APE_ID
                                            ,COV.ACADEMIC_SESSION
                                            ) APE_COURSES_PER_ACADEMIC_YEAR

I'm trying to achieve the same result in SQL Server (our source database uses Oracle but our warehouse uses SQL Server).

I know the distinct isn't supported with window functions in SQL Server 2008 - can anyone suggest an alternative?

On the dba site [Can you use COUNT DISTINCT with an OVER clause?](http://dba.stackexchange.com/a/76761/3690) — Martin Smith, Dec 19 '14 at 12:52
Possible duplicate of [Partition Function COUNT() OVER possible using DISTINCT](https://stackoverflow.com/questions/11202878/partition-function-count-over-possible-using-distinct) — Harish Sripathi, Nov 20 '19 at 14:52

score 10 · Accepted Answer · answered Jun 07 '17 at 17:49

10

Here's what I recently came across. I got it from this post. So far it works really well for me.

DENSE_RANK() OVER (PARTITION BY PartitionByFields ORDER BY OrderByFields ASC) +
DENSE_RANK() OVER (PARTITION BY PartitionByFields ORDER BY OrderByFields DESC) - 1 AS DistinctCount

answered Jun 07 '17 at 17:49

JoeFletch

3,820
1
27
36

GREAT, Thank you! – Joorge Leal Jun 09 '17 at 13:41
3

This includes `NULL` values, whereas `COUNT()` doesn't. – CJ Dennis Jun 22 '17 at 01:34

score 9 · Answer 2 · answered Dec 19 '14 at 13:09

Alas, you cannot do count(distinct) over in SQL Server. You can do this with a subquery. The idea is to enumerate the values within each course code (and subject to the other partitioning conditions). Then, just count up the values where the sequence number is 1:

select sum(case when cc_seqnum = 1 then 1 else 0 end) as APE_COURSES_PER_ACADEMIC_YEAR
from (select . . . ,
             row_number () OVER (PARTITION BY s.REGISTRATION_NUMBER, APEC.APE_ID,
                                              COV.ACADEMIC_SESSION,
                                              APEC.COURSE_CODE
                                 ORDER BY (SELECT NULL)
                                ) as cc_seqnum
      from . . . 
     ) t

You have a complex query. I would suggest that you replace the count(distinct) with the row_number() and make your current query a subquery or CTE for the final query.

Count (Distinct ([value)) OVER (Partition by) in SQL Server 2008

2 Answers2

Linked