conor - 1 year ago 63

SQL Question

I have two tables,

`#Test`

`#Control`

`#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`

`Component`

I would like to aggregate them each to get the sum of

`Price`

`Name`

`Component`

`#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 Source

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 JOIN`

s 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
```

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