SQL query GROUP BY groups

Question

I have something like this:

id	name	totalAmount
1	name1	10
2	name1	20
3	name1	25
4	name2	5
5	name2	12

And need to looks like this:

id's	name	totalAmount
1,2	name1	30
2,3	name1	45
1,3	name1	35
1,2,3	name1	55
4,5	name2	17

I'm using the STRING_AGG but don't know how to separated in the first 3 id's.

I cannot see any patterns in your data or otherwise see **how** your output data can be derived from your source data. Why are some values repeated more than others? Is `name` significant? Why is `1` and `2` combined, and `2` and `3` but not `1` and `3`? — Dai, Nov 25 '21 at 02:25
@IvanC That isn't going to scale: if you have 3 input rows (`a`, `b`, `c`) then you have 4 output rows (`a+b`, `b+c`, `a+c`, and `a+b+c`); if you have 4 input rows (`a`, `b`, `c`, `d`) then you have 9 output rows (`a+b`, `a+c`, `a+d`, `b+c`, `b+d`, `c+d`, `a+b+c`, `b+c+d`, `a+b+c+d`, for 5 input rows the output continues to explode... I doubt you actually want to do that... — Dai, Nov 25 '21 at 02:55

score 1 · Accepted Answer · answered Nov 25 '21 at 15:15

Here is a recursive version which can handle more than 3 ids for a name and returns all possible combinations. As Dai points out though, take care as the number of combinations quickly mushrooms. But if your real data is like your example (normally 2-3 ids per name) than it should be fine.

Worth noting that I did this for fun. Probably you would be best just storing the raw data and doing this kind of shenanigans in the application layer.

CREATE TABLE #data
(
    id INT,
    [name] VARCHAR(10),
    totalAmount INT 
);
INSERT INTO #data
VALUES 
(1, 'name1', 10),
(2, 'name1', 20),
(3, 'name1', 25),
(4, 'name2', 5),
(5, 'name2', 12);

WITH cte (name, ids, maxid, tot) AS
(
    SELECT a.name, 
        CONVERT(VARCHAR(8000), CONVERT(VARCHAR(10), a.id) + ',' + CONVERT(VARCHAR(10), b.id) ) AS ids, 
        b.id AS maxid,
        a.totalAmount + b.totalAmount AS tot
    FROM #data a
    INNER JOIN #data b ON b.name = a.name AND a.id < b.id
    UNION ALL
    SELECT cte.name,
        CONVERT(VARCHAR(8000), cte.ids + ',' +CONVERT(VARCHAR(10), a.id)), 
        a.id AS maxid,
        cte.tot + a.totalAmount
    FROM cte
    INNER JOIN #data a ON cte.name = a.name
    WHERE a.id > cte.maxid
)
SELECT ids, name, tot
FROM cte

score 0 · Answer 2 · answered Nov 25 '21 at 10:23

-- *** Test Data ***
CREATE TABLE #t
(
    id int NOT NULL PRIMARY KEY
    ,[name] nvarchar(30) NOT NULL
    ,totalAmount money NOT NULL
);
INSERT INTO #t
VALUES (1, 'name1', 10)
,(2, 'name1', 20)
,(3, 'name1', 25)
,(4, 'name2', 5)
,(5, 'name2', 12);
-- *** End Test Data ***

SELECT CAST(T1.id AS varchar(10))
        + ',' + CAST(T2.id AS varchar(10)) AS ids
    ,T1.[name] AS [name]
    ,T1.totalAmount + T2.totalAmount AS totalAmount
FROM #t T1
    JOIN #t T2
        ON T1.[name] = T2.[name]
WHERE T1.id < T2.id

UNION ALL

SELECT CAST(T1.id AS varchar(10))
        + ',' + CAST(T2.id AS varchar(10))
        + ',' + CAST(T3.id AS varchar(10)) AS ids
    ,T1.[name] AS [name]
    ,T1.totalAmount + T2.totalAmount + T3.totalAmount AS totalAmount
FROM #t T1
    JOIN #t T2
        ON T1.[name] = T2.[name]
    JOIN #t T3
        ON T1.[name] = T3.[name]
WHERE T1.id < T2.id
    AND T2.id < T3.id;

SQL query GROUP BY groups

2 Answers2