or
sara92 imported from SE
sql-server
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?
Top Answer
Erik Darling
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 & A [CROSS APPLY produces outer join](https://topanswers.xyz/databases?q=256).

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

  [1]: https://feedback.azure.com/forums/908035-sql-server/suggestions/32890510-over-clause-enhancement-request-distinct-clause
SQL counting distinct over partition

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.