I have two simple tables:
indoor
id | timestamp | temp | humi
outdoor
id | timestamp | temp
And two selects which give me time and average temperature grouped by same hour for the last 24 hours:
SELECT DATE_FORMAT(timestamp, '%H:00') AS time, round(avg(temp), 1) as avg_out_temp
FROM outdoor
WHERE timestamp >= now() - INTERVAL 1 DAY
GROUP BY DATE_FORMAT(timestamp, '%Y-%m-%d %H')
ORDER BY timestamp ASC;
SELECT DATE_FORMAT(timestamp, '%H:00') AS time, round(avg(temp), 1) as avg_in_temp
FROM indoor
WHERE timestamp >= now() - INTERVAL 1 DAY
GROUP BY DATE_FORMAT(timestamp, '%Y-%m-%d %H')
ORDER BY timestamp ASC;
And now what I need to do is to group those two results by same hour, with respect to possibility that there can be no records in indoor or outdoor table for whole hour, so I need to get:
time | avg_out_temp | avg_in_temp
11:00 | 12.5 | 21.4
12:00 | 13.9 | null
13:00 | null | 22.4
14:00 | 14.0 | 22.5
I'm working with MariaDB:
mysql Ver 15.1 Distrib 10.1.41-MariaDB, for debian-linux-gnueabihf (armv7l) using readline 5.2
Best Answer
Because both your queries return the same number (and type) of fields, the can be combinied with
UNION ALL
. TheORDER BY
is only needed once at then end of the query.To see the difference between
indoor
andoutdoor
this could be added to your query.