Count Distinct over partition by sql

Question

I have a table like

col1ID  col2String Col3ID Col4String Col5Data
  1        xxx       20      abc     14-09-2018
  1        xxx       20      xyz     14-09-2018
  2        xxx       30      abc     14-09-2018
  2        xxx       30      abc     14-09-2018 

I would like to add column which count how many different strings I have in col4String group by col1ID and col3ID.

So something like

COUNT(DISTINCT (Col4String)) over (partition by col1ID, col3ID)

but it doesn't work, I receive an error

Use of DISTINCT is not allowed with the OVER clause.
Msg 102, Level 15, State 1, Line 23.

I have more columns like col2String, col5Data but they shouldn´t be affected, so I can't use distinct at the beginning of SELECT, and dense_rank() also doen´t seems to work in my case.

Thank You for help.

Solution 1

Try this:

DECLARE @DataSource TABLE
(
    [col1ID] INT
   ,[col2String] VARCHAR(12) 
   ,[Col3ID]  INT
   ,[Col4String]  VARCHAR(12)
   ,[Col5Data] DATE
);

INSERT INTO @DataSource
VALUES (1, 'xxx', 20, 'abc', '2018-09-14')
      ,(1, 'xxx', 20, 'xyz', '2018-09-14')
      ,(2, 'xxx', 30, 'abc', '2018-09-14')
      ,(2, 'xxx', 30, 'abc', '2018-09-14');

SELECT *
     ,dense_rank() over (partition by col1ID, col3ID order by [Col4String])  + dense_rank() over (partition by col1ID, col3ID order by [Col4String] desc) - 1
FROM @DataSource

Solution 2

Obviously distinct is not supported in window function in SQL Server, therefore, you may use a subquery instead. Something along these lines:

 select (
           select COUNT(DISTINCT Col4String) 
           from your_table t2
           where t1.col1ID = t2.col1ID and t1.col3ID = t2.col3ID
        )
 from your_table t1

Comments

[jarlh]

DISTINCT is not a function, i.e. no parentheses needed. Simply do COUNT(DISTINCT Col4String) over ... to make code clearer.

[user0810]

Without parentheses doesn´t work either

[jarlh]

Sorry, I didn't try to answer the question, it was just a general advice.

[Harish Sripathi]

Possible duplicate of Partition Function COUNT() OVER possible using DISTINCT

[user0810]

This works, but my problem is that my_table t1 is a big subquery so it looks not good copying the same code- EDIT - I can use CTE

[user0810]

It looks like I was using dense_rank() in a wrong way - also works!