Reputation: 658
I have two queries that get the:
When I UNION
the queries, I am expecting the two measurements to be in separate columns, but they are actually returned in separate (duplicate) rows. See output below.
SELECT snowfall.cms as now_snow, resorts.*, regions.name
FROM snowfall
INNER JOIN resorts on resorts.id = snowfall.resort_id
INNER JOIN regions ON resorts.region_id = regions.id
WHERE snowfall.timestamp >= SUBDATE( NOW( ) , INTERVAL 1 HOUR )
GROUP BY resorts.id
UNION
SELECT snowfall.cms as 24hr_snow, resorts.*, regions.name
FROM snowfall
INNER JOIN resorts on resorts.id = snowfall.resort_id
INNER JOIN regions ON resorts.region_id = regions.id
WHERE snowfall.timestamp >= SUBDATE( NOW() , INTERVAL 1 DAY)
GROUP BY resorts.id
ORDER BY now_snow DESC
I am getting a result of:
now_snow | resorts.name | ... ======================================== 20 | The Mountain 15 | The Mountain 18 | The Hill 102 | The Hill
But was expecting a result of:
now_snow | 24hr_snow | resorts.name | ... ======================================== 20 | 15 | The Mountain 18 | 102 | The Hill
Is UNION
correct in this scenario? How can I achieve the desired output?
Upvotes: 1
Views: 89
Reputation: 15865
A UNION
will append rows to your query. In order to broaden your query you need a join.
I would suggest a self join in this case. One snowfall for daily, one for hourly. Both joined on resort id and grouped by resort name.
SELECT sum(hourly.cms) as now_snow, sum(daily.cms) as 24hr_snow, resorts.name
FROM
snowfall daily
INNER JOIN resorts on resorts.id = daily.resort_id
INNER JOIN regions ON resorts.region_id = regions.id
INNER JOIN snowfall hourly on resorts.id = hourly.resort_id
WHERE daily.timestamp >= SUBDATE( NOW() , INTERVAL 1 DAY)
AND hourly.timestamp >= SUBDATE( NOW( ) , INTERVAL 1 HOUR )
GROUP BY resorts.name
ORDER BY now_snow DESC
You didnt use an aggregate in your post and mysql may allow for that, but I've added it here for clarity.
Upvotes: 2