tablas - Unir dos consultas sql

unir dos tablas sql server

Question

Tengo dos consultas SQL, donde la primera es:

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;

y el segundo es:

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;

(No importa el ''?'', Representan un parámetro en birt). Lo que quiero lograr es lo siguiente: quiero una consulta SQL que devuelva lo mismo que la primera consulta, pero con una columna adicional (tercera) que se ve exactamente como "Cantidad total de 2008" (desde la segunda consulta).

Answer 1

Algunos DBMS soportan la sintaxis FROM (SELECT ...) AS alias_name .

Piensa en tus dos consultas originales como tablas temporales. Puede consultarlos de la siguiente manera:

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

Answer 2

Esto es lo que funcionó para mí:

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

Usé combinaciones, no uniones (necesitaba columnas diferentes para cada consulta, una combinación lo coloca todo en la misma columna) y solté las comillas (en comparación con lo que estaba haciendo Liam) porque me estaban dando errores.

¡Gracias! ¡No podría haberlo logrado sin esta página! PD: lo siento, no sé cómo vas a obtener tus estados de cuenta formateados con colores. etc.

Answer 3

Prueba esto:

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;

Básicamente, debe UNIRSE a la tabla Incomes2008 con el resultado de su primera consulta.

Answer 4

Puedes usar CTE también como a continuación.

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

Answer 5

Si supone que existen valores para todas las actividades en ambos años, simplemente realice una combinación interna de la siguiente manera

 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

Si no puedes asumir esto, entonces mira haciendo una combinación externa

Answer 6

Yo solo usaría una Union

En su segunda consulta agregue el nombre de columna adicional y agregue un '''' en todas las ubicaciones correspondientes en las otras consultas

Ejemplo

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

Answer 7

quizás no sea la forma más elegante de resolver esto

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;

Answer 8

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