Chapter 5 ■ MySQL priMerTesting the Complex Query
For the complex query, we want to know the plants that need watering today. You have already seen how to
break this query down into parts to construct the SQL statement. Let’s see how the database generates the
results of each part then combine them to validate the query.
Let’s begin with the view we created. Recall, this view returns the ID, soil_status, and count of each
soil_status value for those readings taken today. That is, we should see several rows for those plants that
have more than one soil status value for today.
mysql> SELECT *
-> FROM soil_status_today;
+----+-------------+------------+
| id | soil_status | num_events |
+----+-------------+------------+
| 1 | DRY | 10 |
| 2 | OK | 10 |
| 3 | DRY | 4 |
| 3 | OK | 4 |
| 3 | WET | 2 |
| 4 | OK | 6 |
| 4 | WET | 4 |
| 5 | OK | 10 |
+----+-------------+------------+
8 rows in set (0.00 sec)
Great! Now we know the status of each plant’s soil moisture for today. Notice some plants have more
than one value as their soil moisture changed throughout the day. What we want are those plants that have
more “dry” values than other values. But let’s slow down a bit.
Recall we used a function to calculate the maximum samples for a given plant taken today. Let’s use
that function to zero in on the plant IDs in the previous results. In this case, we see several rows that have a
higher number of one value for soil_status. Let’s use the function to return the percentage of occurrences
for each value found. In this case, I will query for all the plants, calculating the percentages so that we can
see all the data more easily. What we should see are the same rows as before only with the average
samples added.
mysql> SELECT id, soil_status, num_events, (num_events/max_samples_today(id)) as
percent_occurrence
-> FROM soil_status_today;
+----+-------------+------------+--------------------+
| id | soil_status | num_events | percent_occurrence |
+----+-------------+------------+--------------------+
| 1 | DRY | 10 | 1.0000 |
| 2 | OK | 10 | 1.0000 |
| 3 | DRY | 4 | 0.4000 |
| 3 | OK | 4 | 0.4000 |
| 3 | WET | 2 | 0.2000 |
| 4 | OK | 6 | 0.6000 |
| 4 | WET | 4 | 0.4000 |
| 5 | OK | 10 | 1.0000 |
+----+-------------+------------+--------------------+
8 rows in set (0.00 sec)