I have three tables:
tblUnits [Unit_ID, Country]
tblEvents [Unit_ID, Month, Duration_Hrs]
tblMetrics [Unit_ID, Month, Service_Hrs]
and built 3 queries on them:
1.
SELECT Unit_ID
FROM tblUnits
WHERE City = 'NewYork'
with output:
Unit_ID
1
2
3
SELECT
COUNT(IF(Event_Type in ('X', 'Y'), 1, NULL)) as Ev_Count,
SUM(CASE
WHEN Event_Type in ('X', 'Y') THEN Duration_Hrs
END) Duration
FROM tblEvents
WHERE
Design = 'GoodDesign'
AND Month = 'May'
AND Unit_ID in (1,2,3)
with output:
Ev_Count | Duration
1 | 10
SELECT SUM(Service_Hrs) as SumSH
FROM tblMetrics
WHERE Month = 'May'
AND Unit_ID in (1,2,3)
with output:
SumSH
100
Now I would like to combine those 3 queries to get output like this:
Ev_Count | Duration | SumSH
1 | 10 | 100
My query with two left joins multiplies tblEvents and tblMetrics values.
You can't join the queries without getting additional rows which will return wrong aggregations and since your version of MySql does not support CTEs, you have to use twice the 1st query with the operator IN:
SELECT
SUM(Event_Type IN ('X', 'Y')) Ev_Count,
SUM(CASE WHEN Event_Type IN ('X', 'Y') THEN Duration_Hrs END) Duration,
(
SELECT SUM(Service_Hrs)
FROM tblMetrics
WHERE Month = 'May'
AND Unit_ID IN (SELECT Unit_ID FROM tblUnits WHERE City = 'NewYork')
) SumSH
FROM tblEvents
WHERE Design = 'GoodDesign' AND Month = 'May'
AND Unit_ID IN (SELECT Unit_ID FROM tblUnits WHERE City = 'NewYork')
Also, both your aggregated columns Ev_Count and Event_Type use the condition:
Event_Type IN ('X', 'Y')
so why don't you filter for this condition in the WHERE clause and avoid conditional aggregation:
SELECT
COUNT(*) Ev_Count,
SUM(Duration_Hrs) Duration,
(
SELECT SUM(Service_Hrs)
FROM tblMetrics
WHERE Month = 'May'
AND Unit_ID IN (SELECT Unit_ID FROM tblUnits WHERE City = 'NewYork')
) SumSH
FROM tblEvents
WHERE Design = 'GoodDesign' AND Month = 'May'
AND Event_Type IN ('X', 'Y')
AND Unit_ID IN (SELECT Unit_ID FROM tblUnits WHERE City = 'NewYork')