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.

`DISTINCT` is not a function, i.e. no parentheses needed. Simply do `COUNT(DISTINCT Col4String) over ...` to make code clearer. — jarlh, Nov 28 '18 at 10:59
Sorry, I didn't try to answer the question, it was just a general advice. — jarlh, Nov 28 '18 at 11:04
Possible duplicate of [Partition Function COUNT() OVER possible using DISTINCT](https://stackoverflow.com/questions/11202878/partition-function-count-over-possible-using-distinct) — Harish Sripathi, Nov 20 '19 at 14:50

score 8 · Accepted Answer · answered Nov 28 '18 at 11:16

8

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

answered Nov 28 '18 at 11:16

gotqn

42,737
46
157
243

It looks like I was using `dense_rank()` in a wrong way - also works! – user0810 Nov 28 '18 at 11:29
I like that tricky trick. Worked like a charm for me. – Jai Jeffryes Aug 05 '22 at 19:36

score 1 · Answer 2 · answered Nov 28 '18 at 11:11

1

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

answered Nov 28 '18 at 11:11

Radim Bača

10,646
1
19
33

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 Nov 28 '18 at 11:22

score 0 · Answer 3 · answered Nov 28 '18 at 11:10

Try this way;

select * from TableX X
outer apply(select count(*) as stringCount , X2.Col4String 
            from TableX X2 on X.col1ID= X2.col1ID and X.col3ID = X2.col3ID
            group by X2.Col4String ) K

score 0 · Answer 4 · answered Nov 28 '18 at 11:38

0

I would use APPLY :

SELECT t.*, t1.Col4String_Cnt
FROM table t CROSS APPLY
     (SELECT COUNT(DISTINCT t1.Col4String) AS Col4String_Cnt
      FROM table t1
      WHERE t1.col1ID = t.col1ID AND t1.col3ID  = t.col3ID 
     ) t1;

answered Nov 28 '18 at 11:38

Yogesh Sharma

49,870
5
26
52

score 0 · Answer 5 · answered Nov 28 '18 at 11:52

You can do this with an additional level of window functions. One method uses dense_rank():

SELECT . . .,
       MAX(DR) OVER (PARTITION BY col1ID, col3ID)
FROM (SELECT t.*, 
             DENSE_RANK() OVER (PARTITION BY col1ID, col3ID ORDER BY Col4String) as dr
      FROM t
     ) t

Count Distinct over partition by sql

5 Answers5

Linked

Related