Aggregate grouped by values in multiple tables [SQL]

Question

I have two tables, #Test and #Control, that have the same columns as each other and look something like the following:

#Test:                        #Control:
Name  Component  Price        Name  Component  Price
A     a          1.00         A     a          7.00
A     b          2.00         A     a          8.00
A     a          3.00         B     a          9.00
B     a          4.00         B     d          10.00
B     a          5.00         B     d          11.00
B     c          6.00     

but with many more columns and combinations of Name and Component.

I would like to aggregate them each to get the sum of Price per Name and Component, however I want a value for all combinations that appear in either list. The desired output using the example tables above would look like this:

#TestAgg:                        #ControlAgg:
Name  Component  SumPrice        Name  Component  SumPrice
A     a          4.00            A     a          15.00
A     b          2.00            A     b          0.00
B     a          9.00            B     a          9.00
B     c          6.00            B     c          0.00
B     d          0.00            B     d          21.00

How can I do this?

For an individual table the following works:

SELECT Name
       ,Component
       ,sum(Price) as SumPrice                  
INTO #TestAgg
FROM #Test
GROUP BY rollup(Name,Component)
order by 1, SumPrice desc 

However I can't figure out how to return zeros for Name-Component combos that only exist in the other table.

Answer 1

You might try this:

CREATE TABlE #Test(Name VARCHAR(1), Component VARCHAR(1),  Price DECIMAL(14,4));
INSERT INTO #Test VALUES
 ('A','a',1.00)    
,('A','b',2.00)    
,('A','a',3.00)   
,('B','a',4.00)    
,('B','a',5.00)    
,('B','c',6.00);     

CREATE TABlE #Control(Name VARCHAR(1), Component VARCHAR(1),  Price DECIMAL(14,4));
INSERT INTO #Control VALUES
 ('A','a',7.00)
,('A','a',8.00)
,('B','a',9.00)
,('B','d',10.00)
,('B','d',11.00);

--First I use a CTE to get a distinct list of all combinations of both tables

WITH AllCombos AS
(
    SELECT DISTINCT Name,Component
    FROM #Test
    UNION --without "ALL" it will be distinct over the tables
    SELECT DISTINCT Name,Component
    FROM #Control
)

--Now I use LEFT JOINs to get both result-sets --And use a final GROUP BY

SELECT Source,Name,Component,ISNULL(SUM(Price),0) AS Price
FROM
(
    SELECT 'Test' AS Source, AC.Name,AC.Component,T.Price
    FROM AllCombos AS AC
    LEFT JOIN #Test AS T ON AC.Component=T.Component AND AC.Name=T.Name
    UNION ALL
    SELECT 'Control',AC.Name,AC.Component,C.Price
    FROM AllCombos AS AC
    LEFT JOIN #Control AS C ON AC.Component=C.Component AND AC.Name=C.Name
) AS tbl
GROUP BY Source,Name,Component

--Clean-up
GO
DROP TABLE #Test;
DROP TABLE #Control;

The result

Control A   a   15.0000
Control A   b   0.0000
Control B   a   9.0000
Control B   c   0.0000
Control B   d   21.0000
Test    A   a   4.0000
Test    A   b   2.0000
Test    B   a   9.0000
Test    B   c   6.0000
Test    B   d   0.0000

UPDATE

If you really need two tables, you can write

SELECT ... INTO #ControlAgg 
FROM (...) AS tbl 
WHERE Source='Control' 
GROUP BY ...

(Same with Test)

... and call this twice... Or - better in my eyes - you write this in one commong table and use the Source within a query to get them separated...