Using Sum function within Union

[minli98]

Hi,

I am trying to combine data from 2 tables using union.

Suppose I have two tables A and B. They both have the same structure:

Table A:
ProductID Qty
1200 1
1201 2
1200 2
1202 1

Table B:
ProductID Qty
1200 3
1201 1
1202 2
1202 3


So each table can have multiple rows of the same productID. I would like to combine in a query the productIDs along with the sum of the quantity.

ProductID total_qty
1200 6
1201 3
1202 6

My attempt with union is as follows:

select productid, sum(quantity) as total_qty
from tableA
group by productid

union
select productid, sum(quantity) as total_qty
from tableB
group by productid

However I found that union does not sum the total_qty from the two tables. How can I fix that?

Thank you.
Min

 

[ESquared]

select productid, sum(quantity)
from (
select productid, quantity = sum(quantity) as total_qty
from tableA
group by productid

union all -- your query will eventually break if you leave out this keyword
select productid, sum(quantity) as total_qty
from tableB
group by productid
) x
group by productid

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[minli98]

Thanks ESquared, I will give it a try.

Regards,
Min

 

[ESquared]

oops i missed your "as total_qty" bit.

You'll have to take out my "quantity = " and fix other stuff above.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]