database - Join two sql queries

Question

I have two SQL queries, where the first one is:

select Activity, SUM(Amount) as "Total Amount 2009"
from Activities, Incomes
where Activities.UnitName = ? AND
      Incomes.ActivityId = Activities.ActivityID
GROUP BY Activity
ORDER BY Activity;

and the second one is:

select Activity, SUM(Amount) as "Total Amount 2008"
from Activities, Incomes2008
where Activities.UnitName = ? AND
      Incomes2008.ActivityId = Activities.ActivityID
GROUP BY Activity
ORDER BY Activity;

(Dont mind the '?', they represent a parameter in birt). What i want to achieve is the following: I want an SQL query that returns the same as the first query, but with an additional (third) column which looks exactly like "Total Amount 2008" (from the 2nd query).

question from:https://stackoverflow.com/questions/564997/join-two-sql-queries

Answer 1

Some DBMSs support the FROM (SELECT ...) AS alias_name syntax.

Think of your two original queries as temporary tables. You can query them like so:

SELECT t1.Activity, t1."Total Amount 2009", t2."Total Amount 2008"
FROM (query1) as t1, (query2) as t2
WHERE t1.Activity = t2.Activity