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

Vet

Join is a vague term. Do you mean "outer join" or "inner join"? What happens to activities that don't match between the two queries? – Lamonica

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

Macfarlane

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

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... – Cowes

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

Does Mysql support this? – Backstage

@Backstage Yes, it does. – Macfarlane

Answer 2

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

Topnotch

The statement compiles but the computed sums are way to large – Ayrshire

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

Answer 3

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

Canonize

Answer 4

Here's what worked for me:

select visits, activations, simulations, simulations/activations
   as sims_per_visit, activations/visits*100
   as adoption_rate, simulations/activations*100
   as completion_rate, duration/60
   as minutes, m1 as month, Wk1 as week, Yr1 as year 

from
(
    (select count(*) as visits, year(stamp) as Yr1, week(stamp) as Wk1, month(stamp)
    as m1 from sessions group by week(stamp), year(stamp)) as t3

    join

    (select count(*) as activations, year(stamp) as Yr2, week(stamp) as Wk2,
    month(stamp) as m2 from sessions where activated='1' group by week(stamp),
    year(stamp)) as t4

    join

    (select count(*) as simulations, year(stamp) as Yr3 , week(stamp) as Wk3,
    month(stamp) as m3 from sessions where simulations>'0' group by week(stamp),
    year(stamp)) as t5

    join

    (select avg(duration) as duration, year(stamp) as Yr4 , week(stamp) as Wk4,
    month(stamp) as m4 from sessions where activated='1' group by week(stamp),
    year(stamp)) as t6
)
where Yr1=Yr2 and Wk1=Wk2 and Wk1=Wk3 and Yr1=Yr3 and Yr1=Yr4 and Wk1=Wk4

I used joins, not unions (I needed different columns for each query, a join puts it all in the same column) and I dropped the quotation marks (compared to what Liam was doing) because they were giving me errors.

Thanks! I couldn't have pulled that off without this page! PS: Sorry I don't know how you're getting your statements formatted with colors. etc.

Peterkin

Answer 5

You can use CTE also like below.

With cte as
(select Activity, SUM(Amount) as "Total Amount 2009"
   from Activities, Incomes
  where Activities.UnitName = ? AND
        Incomes.ActivityId = Activities.ActivityID
  GROUP BY Activity
),
cte1 as 
(select Activity, SUM(Amount) as "Total Amount 2008"
   from Activities, Incomes2008
  where Activities.UnitName = ? AND
        Incomes2008.ActivityId = Activities.ActivityID
  GROUP BY Activity
)
Select cte.Activity, cte.[Total Amount 2009] ,cte1.[Total Amount 2008]      
  from cte join cte1 ON cte.ActivityId = cte1.ActivityID
 WHERE a.UnitName = ?
 ORDER BY cte.Activity 

Cynde

Answer 6

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.

Nyasaland

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) – Cotto

My initial attempt looked something like this. didnt work though – Ayrshire

Answer 7

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

Erratum

Sorry there may have been an extra semicolon running around. – Erratum

Answer 8

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;

Cowes

Answer 9

It's easy when you see the solution.

With name1 as (your_query_here),
name2 as (your_second_query_here)
select * from name1 JOIN name2.attribute=name1.attribute;

Attribute is on the common values that two SQL queries possess.

Anxious