SQL counting distinct over partition I have a table with two columns, I want to count the distinct values on Col_B over (conditioned by) Col_A.

MyTable

Col_A | Col_B 
A     | 1
A     | 1
A     | 2
A     | 2
A     | 2
A     | 3
b     | 4
b     | 4
b     | 5


Expected Result

Col_A   | Col_B | Result
A       | 1     | 3
A       | 1     | 3
A       | 2     | 3
A       | 2     | 3
A       | 2     | 3
A       | 3     | 3
b       | 4     | 2
b       | 4     | 2
b       | 5     | 2


I tried the following code

select *, 
count (distinct col_B) over (partition by col_A) as 'Result'
from MyTable


count (distinct col_B) is not working.
How can I rewrite the count function to count distinct values?
This is how I'd do it:

SELECT      *
FROM        #MyTable AS mt
CROSS APPLY (   SELECT COUNT(DISTINCT mt2.Col_B) AS dc
                FROM   #MyTable AS mt2
                WHERE  mt2.Col_A = mt.Col_A
                -- GROUP BY mt2.Col_A 
            ) AS ca;


The GROUP BY clause is redundant given the data provided in the question, but may give you a better execution plan. See the follow-up Q &amp; A CROSS APPLY produces outer join.

Consider voting for OVER clause enhancement request - DISTINCT clause for aggregate functions on the feedback site if you would like that feature added to SQL Server.