SQL - Summing with Sub/Main Groups

Question

Let's say I have a table as the following:

     LogicalRef               Code          Balance    
              1             320.01             11.5
              2             320.01                9
              3          320.01.03               10
              4             320.02                7
              5             320.03                0
              6          320.03.01                3
              7          320.03.01                4

I want to get the distinct codes and the sums for each code such that the sum of the sub-groups are added to the main groups. Hence, the SELECT Code, SUM(Balance) FROM table GROUP BY Code does not work.

I want to get the following table:

                Code          SUM
              320.01         30.5
           320.01.03           10
              320.02            7
              320.03            7
           320.03.01            7

I guess there has to be something with LIKE Code + '%', but I could not find any solution up to now. Any help would be appreciated.

Thanks!

Do you happen to have a table that maps Codes to parent codes? — Paul Williams, Mar 23 '15 at 19:02

score 0 · Answer 1 · answered Mar 23 '15 at 19:02

You can use the following query:

SELECT LEFT(code, 6) AS Code, SUM(balance) AS SUM
FROM mytable
GROUP BY LEFT(code, 6)

to get sums for all groups of the first two parts of code:

Code    SUM
-------------
320.01  30.5
320.02  7
320.03  7

Then simply UNION with sums for groups containing the whole of the code:

SELECT code AS Code, SUM(balance) AS SUM
FROM mytable
WHERE LEN(code) > 6
GROUP BY code

Output from above query:

Code        SUM
---------------
320.01.03   10
320.03.01   7

Putting it all in one piece:

SELECT LEFT(code, 6) AS Code, SUM(balance) AS SUM
FROM mytable
GROUP BY LEFT(code, 6)

UNION ALL

SELECT code AS Code, SUM(balance) AS SUM
FROM mytable
WHERE LEN(code) > 6
GROUP BY code

Output:

Code        SUM
------------------
320.01      30.5
320.02      7
320.03      7
320.01.03   10
320.03.01   7

score 0 · Answer 2 · answered Mar 23 '15 at 19:10

The idea is to generate tree(code, parent), join to main table by code and group by parent. This code is not limited to test data and can be applied to more complex tree:

declare @t table(code nvarchar(max), balance money)

insert into @t values
('320.01', 11.5), 
('320.01', 9), 
('320.01.03', 10), 
('320.02', 7),
('320.03', 0),
('320.03.01', 3),
('320.03.01', 4)

;with codes as (select distinct code from @t)
,tree as (select code as parent, code from codes
               union all
              select t.parent, c.code from codes c join tree t on c.code like t.code + '.%')
              select t.parent, sum(c.balance) as balance
              from tree t
              join @t c on t.code = c.code
              group by t.parent

Working fiddle http://sqlfiddle.com/#!6/d32ad/37

SQL - Summing with Sub/Main Groups

2 Answers2