How to merge sums together in an union query 1

[delorfra]

Hi,

In a query such as :

Select myfield1 as myfield, sum(myval1) as totval from mytable1 group by myfield1
Union Select myfield2, sum(myval2) from mytable2 group by myfield2

how can get I get the sum from table to be added to the sum from table2. As it is, the sums don't merge together where the same item is found in both myfield1 and myfield2.

I tried UNION ALL without success.

Thanks.

 

[Cagliostro]

select t.myfield, sum(t.total) as total from
(
Select myfield1 as myfield, sum(myval1) as totval from mytable1 group by myfield1
Union Select myfield2, sum(myval2) from mytable2 group by myfield2
) as t
group by t.myfield John Fill

ivfmd@mail.md

 

[delorfra]

Thanks

That's what I was looking for (In Oracle, I just had to drop the as before the table name to get it to work ie "t" instead of "as t&quot and by the way it solves another problem I have had for quite some time with no working answers given on this site up to now (3 posts) which is : how to divide results coming from two different recordsets without using temporary recordsets.

select (t.NoNCParts/u.NoPartsDelivered) as PPM from
(select field1, field2, sum(NCParts) as NoNCParts from Nonconformities where ...) t,
(select field3, filed4, sum(QtyDelivered) as NoPartsDelivered from deliveries where ...) u

Thanks a lot. I vote for you