Sql-server – SQL counting distinct over partition

countsql serverwindow functions

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?

Best Answer

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.

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.

OBSERVATION #1

If tablename had no indexes, a full table scan would be required.

OBSERVATION #2

If tablename had an index on rank, you still get a full table scan because of the MySQL Query Optimizer ruling out the use of the index because of factors such as key distribution and the possibility of having to lookup each id for every rank during a full index scan.

OBSERVATION #3

If the table had a compound index of (rank,id), then you can a full index scan. In most cases, a full index scan that never references the table for non-indexed columns would be faster than a full index scan that does (See OBSERVATION #2)

OBSERVATION #4

If the query was written slightly different

SELECT rank, COUNT(1) FROM tablename GROUP BY rank

then an index on just the rank column would suffice and produce a full index scan.

CONCLUSION

In light of these observtions, it is definitely a thing of beauty to present to the MySQL Query Optimizer two things:

a good query
proper indexes for all tables in the query

In retrospect, it is also good to give the MySQL Query Optimizer as much of an advantage upfront as possible.

MySQL count multiple columns and sum the total occurrence

This is a query with an idea like in MySQL count multiple columns and sum the total occurrence

SELECT 
drawno, 
SUM(CASE WHEN FIRST = 0 THEN 1 ELSE 0 END) zero,
SUM(CASE WHEN FIRST = 1 THEN 1 ELSE 0 END) one,
SUM(CASE WHEN FIRST = 2 THEN 1 ELSE 0 END) two,
SUM(CASE WHEN FIRST = 3 THEN 1 ELSE 0 END) three,
SUM(CASE WHEN FIRST = 4 THEN 1 ELSE 0 END) four,
SUM(CASE WHEN FIRST = 5 THEN 1 ELSE 0 END) five,
SUM(CASE WHEN FIRST = 6 THEN 1 ELSE 0 END) six,
SUM(CASE WHEN FIRST = 7 THEN 1 ELSE 0 END) seven,
SUM(CASE WHEN FIRST = 8 THEN 1 ELSE 0 END) eight,
SUM(CASE WHEN FIRST = 9 THEN 1 ELSE 0 END) nine
FROM 
(
  SELECT 
    first
    ,drawno 
  FROM table1 
  UNION ALL 
  SELECT 
     second
     ,drawno 
  FROM table1 
  UNION ALL 
  SELECT 
    third
    ,drawno 
  FROM table1 
  UNION ALL 
  SELECT 
    fourth
    ,drawno 
  FROM table1
) AS t GROUP BY drawno;

Two queries, this and from first answer: http://sqlfiddle.com/#!2/2eb93d/2