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

User · Answer

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 gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Join two sql queries

Examples related to sql

Examples related to database

Examples related to join