SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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)
16 rows affected

   SELECT Nationality, SUM(d.Number) AS GroupOf
   FROM Data d
   GROUP BY Nationality


Nationality
GroupOf


English
3

Franch
5

Italian
7

Spanish
9


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);


Nationality
GroupOf


English
3

Franch
4

Franch
1

Italian
4

Italian
3

Spanish
4

Spanish
4

Spanish
1

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