Join two sql queries

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

From stackoverflow

• Try this:

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

  Basically you have to JOIN Incomes2008 table with the output of your first query.

  Kjetil Watnedal : Because you have used inner joins, the result will not contain rows with no income in 2008 or no income in 2009 (ie not having a row in the income table)

  Shivasubramanian A : Guess I was in a hurry to post... Shd work now...

  Schildmeijer : My initial attempt looked something like this. didnt work though

• SELECT Activity, arat.Amount "Total Amount 2008", abull.Amount AS "Total Amount 2009"
  FROM
    Activities a
  LEFT OUTER JOIN
    (
    SELECT ActivityId, SUM(Amount) AS Amount
    FROM Incomes ibull
    GROUP BY
      ibull.ActivityId
    ) abull
  ON abull.ActivityId = a.ActivityID
  LEFT OUTER JOIN
    (
    SELECT ActivityId, SUM(Amount) AS Amount
    FROM Incomes2008 irat
    GROUP BY
      irat.ActivityId
    ) arat
  ON arat.ActivityId = a.ActivityID
  WHERE a.UnitName = ?
  ORDER BY Activity
  

  Schildmeijer : The statement compiles but the computed sums are way to large

  Quassnoi : Sorry, didn't get what you want on the first time. See updated post.

• I would just use a Union

  In your second query add the extra column name and add a '' in all the corresponding locations in the other queries

  Example

  //reverse order to get the column names
  select top 10 personId, '' from Telephone//No Column name assigned 
  Union 
  select top 10 personId, loanId from loan
  

• If you assume that values exist for all activities in both years then just do an inner join as follows

   select act.activity, t1.amount as "Total 2009", t2.amount as "Total 2008"
  from Activities as act,
      (select activityid,  SUM(Amount) as amount
      from Activities, Incomes
      where Activities.UnitName = ? AND
            Incomes.ActivityId = Activities.ActivityID
      GROUP BY Activityid) as t1,
      (select activityid, SUM(Amount) as amount
      from Activities, Incomes2008
      where Activities.UnitName = ? AND
            Incomes2008.ActivityId = Activities.ActivityID
      GROUP BY Activityid) as t2
      WHERE t1.activityid= t2.activityid
      AND act.activityId = t1.activityId
      ORDER BY act.activity
  

  If you can't assume this, then look at doing an outer join

  Il-Bhima : Sorry there may have been an extra semicolon running around.

• perhaps not the most elegant way to solve this

  select  Activity, 
       SUM(Amount) as "Total_Amount",
       2009 AS INCOME_YEAR
  from    Activities, Incomes
  where Activities.UnitName = ? AND
        Incomes.ActivityId = Activities.ActivityID
  GROUP BY Activity
  ORDER BY Activity;
  
  UNION
  
  select  Activity, 
       SUM(Amount) as "Total_Amount",
       2008 AS INCOME_YEAR
  from Activities, Incomes2008
  where Activities.UnitName = ? AND
        Incomes2008.ActivityId = Activities.ActivityID
  GROUP BY Activity
  ORDER BY Activity;
  

• 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
  

  Schildmeijer : the problem is though, that if one activity is zero, that activity will not be displayed. possbile to fix that with this elegant solution?

  Natrium : if the Activity were (null) instead of 0, it would be a piece of cake with a left outer join. But in this case it is rather difficult I'm afraid...

  Liam : Maybe you could union this query with a special case for a zero on the left and one for a zero on the right.