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?
TopAnswers
Databases
Top Answer
meme (imported from SE)
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