If you want to retrieve data from related tables simultaneously, SQL JOIN tables queries will be useful. SQL JOIN tables queries can be divided into four main types: INNER JOIN. LEFT JOIN.
You need to create two separate queries and join their result not JOIN their tables. Show activity on this post. JOIN and UNION are differents. In your query you have used a CROSS JOIN operation, because when you use a comma between two table you apply a CROSS JOIN.
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
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
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
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.
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
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With