SQL for joining aggregates and summary 1

[Borvik]

I have a couple of queries that return the same table structure:

Result A

productid    full    partial
---------    ----    -------
1             2        0
2             0        1
3             0        1

Result B

productid    full    partial
---------    ----    -------
1             1        0
3             1        1
4             1        0

What I'm trying to do is join the two results into one set, that gives me a sum of the full and partial columns for each productid, and a count of the productids. So the result would look something like this

Result Final

productid  count  full    partial
---------  -----  ----    -------
1            2     3        0
2            1     0        1
3            2     1        2
4            1     1        0

Anybody able to think of how I might be able to do this?

Thanks.

 

[gmmastros]

try this:

Select ProductId, Count(*) As [Count] , Sum(Full) As full, Sum(Partial) As Partial
From   (
       Select productId, Full, Partial
       From   ResultA

       Union All

       Select productId, Full, Partial
       From   ResultB
       ) As AliasName
Group By ProductId

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Borvik]

You know - I was thinking about that when I wrote the question, my brain must not have been working yesterday.

My big worry was that the Full and Partial fields themselves.

It does appear to work - though with 1.1 million results it's difficult to tell (separately it was 1.2 so it does look consolidated).

Thanks.

 

[Borvik]

*edit*
My big worry was that the Full and Partial fields were aggregate fields themselves.