Chapter 5 ■ MySQL priMer
The following WHERE clause demonstrates how to do this. Notice we pass in the event_time column to
the function, which then determines the date. We compare this to the current date and thus find only those
rows where the sensor reading was taken today.
WHERE DATE(event_time) = CURRENT_DATE()
Now we just need to do the average. We’ve already seen an example earlier, so the following SQL
command should look familiar—at least in concept:
SELECT id, AVG(temperature) as avg_temp FROM readings WHERE DATE(event_time) =
CURRENT_DATE() GROUP BY id;
This will give us the average temperature and the ID for each plant for those readings taken today. All
that is left is to join this information with the information in the plants table to get the name, location, and
average temperature. The following SQL statement shows how to do this:
SELECT name, location, AVG(temperature) as avg_temp
FROM plants JOIN readings ON plants.id = readings.id
WHERE DATE(event_time) = CURRENT_DATE()
GROUP BY plants.id;
Wow, there is a lot going on here for such a simple query! I placed each clause on a separate line to
make it easier to read. Let’s review each.
First, we see the columns selected are the plant name and location as well as the average temperature.
Recall the GROUP BY clause is what controls which rows are supplied to the function. In this case, we group
by the plant ID. Notice the FROM clause performs a join between the plants table and the readings table.
This is so that we can get the plant information from the plants table but perform our calculations (average
temperature) on the data from the readings table that matches the plants table. Thus, a simple join! Finally,
we use the WHERE clause described earlier to restrict the data to only those samples taken today.
But we’re not done. We haven’t answered the question in its entirety. That is, we also want to know the
average temperature for the outside plants only. All we need to do then is add that condition to the WHERE
clause as follows:
SELECT name, location, AVG(temperature) as avg_temp
FROM plants JOIN readings ON plants.id = readings.id
WHERE DATE(event_time) = CURRENT_DATE() AND plants.climate = 2
GROUP BY plants.id;
Now, we’re done! To query for the inside plants, just change the climate value to 1. Recall this is the
value of the enumerated data type for the climate column.
Now let’s look at a much more complex query. At first glance, this will see simple but as you shall see
has many layers.
■Note What follows is just one example of how to form the query. there are several others, some more
optimal, but i want to demonstrate the logical thought process you could take to solve the problem. Feel free to
experiment and improve the following example.