SQL Group By Modulo of Row Count

Question

I have the following sample data:

Id     Name     Quantity
1      Red      1
2      Red      3
3      Blue     1
4      Red      1
5      Yellow   3

So for this example, there are a total of 5 Red, 1 Blue, and 3 Yellow. I am looking for a way to group them by Color, but with a maximum of 2 items per group (sorting is not important). Like so:

Name     QuantityInPackage
Red      2
Red      2
Red      1
Blue     1
Yellow   2
Yellow   1

Any suggestions on how to accomplish this using T-SQL on MS-SQL 2005?

Answer 1

I would define a table containing sequential numbers, say 1 to 1000 and join that table (unless your database supports generating these numbers in the query like Oracle using CONNECT BY):

Table num

n
1
2
3
...

I tried the following query using Oracle (should work with TSQL too):

With summed_colors As (
  Select name, Sum(quantity) quantity
  From colors
  Group By name
)
Select
  name,
  Case When n*2-1 = quantity Then 1 Else 2 End quantityInPackage
From summed_colors
Join nums On ( n*2-1 <= quantity )
Order By name, quantityInPackage Desc

and it returns

Blue   1
Red    2
Red    2
Red    1
Yellow 2
Yellow 1

Answer 2

You need to use a numbers table to unpivot your data to make multiple rows:

DECLARE @PackageSize AS int
SET @PackageSize = 2

DECLARE @numbers AS TABLE (Number int)
INSERT  INTO @numbers
VALUES  (1)
INSERT  INTO @numbers
VALUES  (2)
INSERT  INTO @numbers
VALUES  (3)
INSERT  INTO @numbers
VALUES  (4)
INSERT  INTO @numbers
VALUES  (5)
INSERT  INTO @numbers
VALUES  (6)
INSERT  INTO @numbers
VALUES  (7)
INSERT  INTO @numbers
VALUES  (8)
INSERT  INTO @numbers
VALUES  (9)
INSERT  INTO @numbers
VALUES  (10)

DECLARE @t AS TABLE
    (
     Id int
    ,Nm varchar(6)
    ,Qty int
    )
INSERT  INTO @t
VALUES  (1, 'Red', 1)
INSERT  INTO @t
VALUES  (2, 'Red', 3)
INSERT  INTO @t
VALUES  (3, 'Blue', 1)
INSERT  INTO @t
VALUES  (4, 'Red', 1)
INSERT  INTO @t
VALUES  (5, 'Yellow', 3) ;
WITH    Totals
          AS (
              SELECT    Nm
                       ,SUM(Qty) AS TotalQty
                       ,SUM(Qty) / @PackageSize AS NumCompletePackages
                       ,SUM(Qty) % @PackageSize AS PartialPackage
              FROM      @t
              GROUP BY  Nm
             )
    SELECT  Totals.Nm
           ,@PackageSize AS QuantityInPackage
    FROM    Totals
    INNER JOIN @numbers AS numbers
            ON numbers.Number <= Totals.NumCompletePackages
    UNION ALL            
    SELECT  Totals.Nm
           ,PartialPackage AS QuantityInPackage
    FROM    Totals
    WHERE PartialPackage <> 0

Answer 3

It's not grouping or modulo/division that's the hard part here, it's the fact that you need to do an aggregate (sum) and then explode the data again. There aren't actually any "Red 2" rows, you have to create them somehow.

For SQL Server 2005+, I would probably use a function do the "exploding":

CREATE FUNCTION dbo.CreateBuckets
(
    @Num int,
    @MaxPerGroup int
)
RETURNS TABLE
AS RETURN
WITH First_CTE AS
(
    SELECT CASE
        WHEN @MaxPerGroup < @Num THEN @MaxPerGroup
        ELSE @Num
    END AS Seed
),
Sequence_CTE AS
(
    SELECT Seed AS [Current], Seed AS Total
    FROM First_CTE

    UNION ALL

    SELECT
        CASE
            WHEN (Total + @MaxPerGroup) > @Num THEN (@Num - Total)
            ELSE @MaxPerGroup
        END,
        Total + @MaxPerGroup
    FROM Sequence_CTE
    WHERE Total < @Num
)
SELECT [Current] AS Num
FROM Sequence_CTE

Then, in the main query, group (sum) the data first and then use the bucket function:

WITH Totals AS
(
    SELECT Name, SUM(Quantity) AS Total
    FROM Table
    GROUP BY Name
)
SELECT Name, b.Num AS QuantityInPackage
FROM Totals
CROSS APPLY dbo.CreateBuckets(Total, 2) b

This should work for any bucket size, doesn't have to be 2 (just change the parameter).

Answer 4

This is very crude, but it works.

CREATE TABLE #Colors
    (
    Id int,
    Name varchar(50),
    Quantity int
    )

INSERT INTO #Colors VALUES (1, 'Red', 1)
INSERT INTO #Colors VALUES (2, 'Red', 3)
INSERT INTO #Colors VALUES (3, 'Blue', 1)
INSERT INTO #Colors VALUES (4, 'Red', 1)
INSERT INTO #Colors VALUES (5, 'Yellow', 3)
INSERT INTO #Colors VALUES (6, 'Green', 2)

SELECT
    Name,
    SUM(Quantity) AS TotalQuantity
INTO #Summed
FROM
    #Colors
GROUP BY
    Name

SELECT
    Name,
    TotalQuantity / 2 AS RecordsWithQuantity2,
    TotalQuantity % 2 AS RecordsWithQuantity1
INTO #SortOfPivot
FROM
    #Summed
ORDER BY
    Name

DECLARE @RowCount int
SET @RowCount = (SELECT COUNT(*) FROM #SortOfPivot)
DECLARE @Name varchar(50)
DECLARE @TwosInsertCount int
DECLARE @OnesInsertCount int

CREATE TABLE #Result (Name varchar(50), Quantity int)

WHILE @RowCount > 0
BEGIN
    SET @Name = (SELECT TOP 1 Name FROM #SortOfPivot)
    SET @TwosInsertCount = (SELECT TOP 1 RecordsWithQuantity2 FROM #SortOfPivot)
    SET @OnesInsertCount = (SELECT TOP 1 RecordsWithQuantity1 FROM #SortOfPivot)
    WHILE @TwosInsertCount > 0
    BEGIN
        INSERT INTO #Result (Name, Quantity) VALUES (@Name, 2)
        SET @TwosInsertCount = @TwosInsertCount - 1
    END
    WHILE @OnesInsertCount > 0
    BEGIN
        INSERT INTO #Result (Name, Quantity) VALUES (@Name, 1)
        SET @OnesInsertCount =  @OnesInsertCount - 1
    END
    DELETE FROM #SortOfPivot WHERE Name = @Name
    SET @RowCount = (SELECT COUNT(*) FROM #SortOfPivot)
END

SELECT * FROM #Result
DROP TABLE #Colors
DROP TABLE #Result
DROP TABLE #Summed
DROP TABLE #SortOfPivot