aggregate function on union query

G

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.
 
G

Gerald Stanley

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

Hope This Helps
Gerald Stanley MCSD
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top