Search code examples
sql-serverdividegroup

Divide Ids into multiple groups based on a number in SQL Server

I'm trying to divide records of data into groups.

Each group size has 4 records, and the last group maybe less than 4, with numbering rows in each group, and count Ids in each group.

The final result should be like this

Final Result

I tried something like this

declare @t int 
set @t = (select count(*) from mytable) 

declare @s int 
set @s = 4

select 
    t.Id
    ,@s over (order by t.Id asc) as GroupId 
    ,case when (row_number() over (order by t.Id asc ) %@s ) = 0 then 4 else (row_number() over (order by t.Id asc) %@s ) end  as RowNum
    ,(count Ids in each group) IdCount
from
    mytable t
group by 
    t.Id
order by 
    t.Id asc
Solution

  • You may use ROW_NUMBER() OVER (ORDER BY ID) function divided by 4 to define the id groups as the following (If you don't have duplicates in ID):

    WITH IdGroups AS
(
  SELECT ID,
         CEILING(ROW_NUMBER() OVER (ORDER BY ID) *1.0/4) GRP
  FROM table_name
)
SELECT ID, GRP AS GroupID, ROW_NUMBER() OVER (PARTITION BY GRP ORDER BY ID) AS ROWNUM, 
       COUNT(*) OVER (PARTITION BY GRP) AS IDCount
FROM IdGroups
ORDER BY ID

    See a demo.

    If you have repeated IDs and you want to count repeated Ids as 1 record, and give them the same row number (same rank), you may use DENSE_RANK function instead of ROW_NUMBER.

    WITH IdGroups AS
(
  SELECT ID,
         CEILING(DENSE_RANK() OVER (ORDER BY ID) *1.0/4) GRP
  FROM table_name
)
SELECT ID, GRP AS GroupID, ROW_NUMBER() OVER (PARTITION BY GRP ORDER BY ID) AS ROWNUM, 
       DENSE_RANK() OVER (PARTITION by GRP ORDER BY ID) 
      + DENSE_RANK() OVER (PARTITION BY GRP ORDER BY ID DESC) 
      - 1 AS IDCount
FROM IdGroups
ORDER BY ID

    Check this demo.