SQL Server 2008 how to Subtotal group

Question

I am trying to add subtotal in query for the following table :

Table A:          Table B:                     Table C:

ID    Name        ID     Name      SubOf       ID      Name      SubOf
===   ======      =====  =======   =====       ======  ======    =====
1     Name 1      1.1    SName 1   1           1.1.1   SSName 1  1.1
2     Name 2      1.2    SName 2   2           1.1.2   SSName 2  1.1
3     Name 3      1.3    SName 3   3           1.1.3   SSName 3  1.1

Table Transaction:

ID        Qty
======    =======
1.1.1     100
1.1.2     50
1.1.3     200
1.1.1     100
1.1.2     50
1.1.3     200
1.1.1     100
1.1.2     50
1.1.3     200
1.1.1     100
1.1.2     50
1.1.3     200
1.1.1     100

I want to select my data like this :

Desired results:

ID        Name        Total
======    =======     =======
1         Name 1      1500    ->Total ID 1 
1.1       sName 1     1500    ->Total ID 1.1
1.1.1     ssName 1     500
1.1.2     ssName 2     200
1.1.3     ssName 3     800

How can I add subtotal like the result table?? Thanks in advance.

Answer 1

use GROUP BY to sum the qty, UNION ALL to combine different grouping in smae result and finnaly CTE for the base query

; with cte as
(
    select  ID1 = a.ID, Name1 = a.Name,
            ID2 = b.ID, Name2 = b.Name,
            ID3 = c.ID, Name3 = c.Name,
            t.Qty
    from    Trans t
            inner join tableC c on  t.ID    = c.ID
            inner join tableB b on  c.SubOf = b.ID
            inner join tableA a on  b.SubOf = a.ID
)
select  ID = ID1, Name = Name1, sum(Qty)
from    cte
group by ID1, Name1
union all
select  ID = ID2, Name = Name2, sum(Qty)
from    cte
group by ID2, Name2
union all
select  ID = ID3, Name = Name3, sum(Qty)
from    cte
group by ID3, Name3