Chapter 5 ■ MySQL priMer
■Note i will enter the query by parts so you can read the results better. as you can see, it is much easier to
read in the MySQL client this way, and the client will “wait” until you type the semicolon to execute the query.
mysql> SELECT * FROM plants
-> WHERE climate = 2;
+----+---------------------+----------+---------+
| id | name | location | climate |
+----+---------------------+----------+---------+
| 1 | Jerusalem Cherry | deck | outside |
| 2 | Moses in the Cradle | patio | outside |
+----+---------------------+----------+---------+
2 rows in set (0.00 sec)
So, we see there are two plants outside, one on the deck and another on the patio. Now, what is the
average temperature of each plant’s readings for today?
mysql> SELECT id, AVG(temperature)
-> FROM readings
-> WHERE DATE(event_time) = CURRENT_DATE() GROUP BY id;
+----+--------------------+
| id | AVG(temperature) |
+----+--------------------+
| 1 | 38.89000015258789 |
| 2 | 38.12999954223633 |
| 3 | 32.859999656677246 |
| 4 | 33.21000003814697 |
| 5 | 38.89000015258789 |
+----+--------------------+
5 rows in set (0.00 sec)
Let’s combine the two to make sure we get exactly what we expect from the test data. We should first
examine the data and try to determine what we should see. In this case, we should see one plant with an
average temperature of 38.89 and another with 38.13 (rounding up).
mysql> 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;
+---------------------+----------+-------------------+
| name | location | avg_temp |
+---------------------+----------+-------------------+
| Jerusalem Cherry | deck | 38.89000015258789 |
| Moses in the Cradle | patio | 38.12999954223633 |
+---------------------+----------+-------------------+
2 rows in set (0.00 sec)
Yes! Now we know that query gets the results we want. What about that other, complex query?