Combine 2 result sets & SUM with GROUP BY without a TempTable

[johncnoland]

I am generating records to back fill an Excel Pivot Report. Three key columns on the left with numerous data columns across the top. When I pull a SQL result set, not all keys are accounted for, so I have some cells with data and some with blanks. I can automatically zero fill the missing cells by creating two result sets and summing them together. The first result set has all keys represented and zero in the value field and the second result set has the actual data.

I keep reading that temporary tables are the evil step-sister of the SQL Server world, but I can't see a way to sum these result sets together without saving the output to a table and then SELECTing a SUMmed result set from the combined SELECTS.

Here is what I have so far:

SELECT DISTINCT t.ActivityDate, t.BookID, t.DMA, StudyData.Driver, 0 as NumValue
INTO #StackDataZeroFill
FROM StudyData, StudyKeys t
WHERE (((StudyData.IsNumber)=1)
AND ((StudyData.ActivityDate) Is Null)
AND ((StudyData.DMA) Is Null))
UNION
SELECT StudyKeys.ActivityDate, StudyData.BookID, StudyKeys.DMA, StudyData.Driver, StudyData.NumValue
FROM StudyData INNER JOIN StudyKeys ON
StudyData.BookID = StudyKeys.BookID
WHERE (((StudyData.IsNumber)=1)
AND ((StudyData.ActivityDate) Is Null)
AND ((StudyData.DMA) Is Null))

SELECT ActivityDate, BookID, DMA, Driver, Sum(NumValue)
FROM #StackDataZeroFill
GROUP BY ActivityDate, BookID, DMA, Driver

Can you sum a unioned result set? Or could I write this differently to get what I want?
John

 

[JD1866DSC]

can you sum a unioned result set, the answer is yes easily with a derived table

select
sum(amount)
from
(
select
amount
from
tablea
union
select
amount
from
tableb
)a

 

[johncnoland]

Thanks for your response. I have been away from SQL Server for over 6 years (version 6.5!) so I have a lot of catching up to do.

I have been doing a lot of ready here!