Count number of unique occurrences of a key value corresponding to each ID column-CodePudding

I have a table in DB2 as below :

Key     ID  SubID
Abc123  576  10
Abc123  576  12
Abc124  576  13
Abc125  577  14
Abc126  578  15
Abc127  578  16
Abc128  578  17

Want to create a additional count column where it counts number of unique occurrences of key value for each ID and the output should be as below

Key     ID  SubID Count
Abc123  576  10    2
Abc123  576  12    2
Abc124  576  13    2
Abc125  577  14    1
Abc126  578  15    3
Abc127  578  16    3
Abc128  578  17    3

I tried below

select Key, ID, SubId ,
count(Key) over (partition by Key) as count
from table

Appreciate any help!

CodePudding user response：

You cannot use a window function with the DISTINCT qualifier. You can use a scalar subquery to count the rows you want.

For example:

select *,
  (select count(distinct key) from t x where x.id = t.id) as cnt
from t

Result:

 KEY     ID   SUBID  CNT 
 ------- ---- ------ --- 
 Abc123  576  10     2   
 Abc123  576  12     2   
 Abc124  576  13     2   
 Abc125  577  14     1   
 Abc126  578  15     3   
 Abc127  578  16     3   
 Abc128  578  17     3

See running example at db<>fiddle.