conor conor - 4 months ago 9
SQL Question

Aggregate grouped by values in multiple tables [SQL]

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

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...