Group or split rows by value

Question

I have a list of person by nationality. These people must be grouped or divided if their number is greater or less than 4 in group of max 4 person with same nationality.

Nationality	Number
Italian	7
Franch	2
Franch	3
English	2
English	1
Spanish	9

The result that I wont is :

Nationality	groupOf
Italian	4
Italian	3
franch	4
franch	1
English	3
Spanish	4
Spanish	4
Spanish	1

The goal is to get groups of 4 or less. For example if I have 41 rows of Swedish with Number=1 the goal is 10 rows of Swedish with GroupOf = 4 plus one row with groupOf = 1.

Zhorov · Accepted Answer · 2022-07-27T10:46:07.240

You need to multiple (using a tally/numbers table) and number the rows, and then use an additional calculation (with the appropriate grouping):

Sample data and a tally table:

SELECT *
INTO Data
FROM (VALUES
   ('Italian', 7),
   ('Franch',  2),
   ('Franch',  3),
   ('English', 2),
   ('English', 1),
   ('Spanish', 9)
) v (Nationality, [Number])
SELECT *
INTO Tally
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) t (Rn)

Statement:

SELECT Nationality, COUNT(*) AS GroupOf
FROM (
   SELECT Nationality, ROW_NUMBER() OVER (PARTITION BY d.Nationality ORDER BY Nationality) AS Rn
   FROM Data d
   JOIN Tally t ON t.Rn <= d.[Number]
) t
GROUP BY Nationality, ((Rn - 1) / 4)

Result:

Nationality	GroupOf
English	3
Franch	4
Franch	1
Italian	4
Italian	3
Spanish	4
Spanish	4
Spanish	1

Charlieface · Answer 2 · 2022-07-27T08:47:51.717

1

Another option utilizing a tally/numbers table, is to group first, then explode the rows by joining the tally

SELECT
  d.Nationality,
  CASE WHEN t.Rn <= (d.GroupOf / 4)
    THEN 4
    ELSE d.GroupOf - ((t.Rn - 1) * 4)
  END AS GroupOf
FROM (
   SELECT Nationality, SUM(d.Number) AS GroupOf
   FROM Data d
   GROUP BY Nationality
) d
JOIN Tally t ON t.Rn <= ((d.GroupOf + 3) / 4);

db<>fiddle

edited Jul 27 '22 at 08:47

answered Jul 27 '22 at 08:41

Charlieface

52,284
6
19
43

score 1 · Answer 3 · answered Jul 27 '22 at 09:31

You could use a recursive CTE:

WITH 
  t AS (SELECT Nationality, SUM(Number) total FROM tablename GROUP BY Nationality),
  cte AS (
    SELECT Nationality, 
           total - CASE WHEN total <= 4 THEN total ELSE 4 END total, 
           CASE WHEN total <= 4 THEN total ELSE 4 END groupOf
    FROM t
    UNION ALL
    SELECT Nationality, 
           total - CASE WHEN total <= 4 THEN total ELSE 4 END total, 
           CASE WHEN total <= 4 THEN total ELSE 4 END groupOf 
    FROM cte
    WHERE total > 0
  )
SELECT Nationality, groupOf 
FROM cte
ORDER BY Nationality, groupOf DESC
OPTION (MAXRECURSION 0); -- remove this line if Numbers are not greater than 400 = 4 * 100

See the demo.

Group or split rows by value

3 Answers3