Search code examples
sqlsql-serverrow-number

Add column to count number of sub values under main value

I have a dataset example as below and would like to add an extra column that will show the number of subblocks in a block in the example further down.

I have played around with rown_number but have been unsuccessful. ROW_NUMBER() over (PARTITION BY BlockRef,SubBlockRef ORDER BY BlockRef, SubBlockRef DESC)

BlockRef    Blocks  SubBlockRef SubBlocks
37391   BLOCK   49391   SUBB
37391   BLOCK   49391   SUBB
37391   BLOCK   49391   SUBB
37391   BLOCK   49391   SUBB
37391   BLOCK   49391   SUBB
37391   BLOCK   49391   SUBB
37391   BLOCK   49392   SUBB
37391   BLOCK   49392   SUBB
37391   BLOCK   49392   SUBB
37391   BLOCK   49392   SUBB
37391   BLOCK   49392   SUBB
37391   BLOCK   49392   SUBB

enter image description here

Any help appreciated

Solution

  • You can use count(distinct) as a window function:

    select t.*,
       count(distinct SubBlockRef) over (partition by BlockRef) as num_subblocks
from t;

    Although standard functionality, not all databases support distinct in window functions. There is a simple workaround using dense_rank():

    select t.*,
       ( dense_rank() over (partition by BlockRef order by SubBlockRef) +
         dense_rank() over (partition by BlockRef order by SubBlockRef desc) - 1
       ) as num_subblocks
from t;

    Given that there is such a simple work-around, it is surprising that many databases do not support the functionality.