aggregate function on union query

[george]

Hi all,

I have this union query:

SELECT t1.price
FROM t1
UNION
SELECT t2.price
FROM t2;

When I run the query it gives me a list of prices from the
two tables (as expected). Now I would like to have a
single sum of all these prices. How do I do this?

Thanks in advance, George.

 

[Gerald Stanley]

SELECT Sum(Price)
FROM
(SELECT t1.price
FROM t1
UNION
SELECT t2.price
FROM t2)

Hope This Helps
Gerald Stanley MCSD

 

[Tom Ellison]

Dear George:

How about:

SELECT SUM(price) FROM (
SELECT t1.price FROM t1
UNION
SELECT t2.price FROM t2
) X

The X on the end is an alias for the "subquery" which came from your
original query. It is generally mandatory.

There is a potential large problem with your original query. If any
price exists in both t1 and t2, it will not show up twice in the
result. UNION eliminates duplicates between the two sides! I propose
you probably want UNION ALL for your purposes. This may well result
in a different result for the SUM.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts