Chapter 5 ■ MySQL priMer
Great, seven rows! Now, we’re getting somewhere. Now let’s limit the output of that result with only
those that have a higher than 50 percent occurrence.
mysql> SELECT id, soil_status, num_events, (num_events/max_samples_today(id)) as
percent_occurrence
-> FROM soil_status_today
-> WHERE (num_events/max_samples_today(id)) > 0.50;
+----+-------------+------------+--------------------+
| id | soil_status | num_events | percent_occurrence |
+----+-------------+------------+--------------------+
| 1 | DRY | 10 | 1.0000 |
| 2 | OK | 10 | 1.0000 |
| 4 | OK | 6 | 0.6000 |
| 5 | OK | 10 | 1.0000 |
+----+-------------+------------+--------------------+
4 rows in set (0.00 sec)
Notice all we did was add a WHERE clause. Now we’ve got those rows for readings taken today that have a
higher than 50 percent occurrence of a single value for soil_status. Let’s expand that query one more time
and add the condition for soil_status = 'DRY'.
■Tip Did you notice something there? Look at soil_status = 'DRY'. Notice anything weird about that?
yep, it’s an enumerated column, and i used one of the values instead of a numeric value like previous examples.
as you can see, so long as the values are listed in the enumeration, you can use either the numeric or text
value. you will get an error if the text does not match one of the enumerated values.
mysql> SELECT id, soil_status, num_events, (num_events/max_samples_today(id)) as
percent_occurrence -> FROM soil_status_today
-> WHERE (num_events/max_samples_today(id)) > 0.50 AND soil_status = 'DRY';
+----+-------------+------------+--------------------+
| id | soil_status | num_events | percent_occurrence |
+----+-------------+------------+--------------------+
| 1 | DRY | 10 | 1.0000 |
+----+-------------+------------+--------------------+
1 row in set (0.00 sec)
Perfect! We’re almost there. Now, we want to know the name and location of that plant, id = 1. For this,
we add a new join to get the information from the plants table. Notice I use the numeric value for the soil
status column.
mysql> SELECT plants.id, name, location, soil_status, num_events, (num_events/maxsamples
today(plants.id)) as percent_occurrence
-> FROM soil_status_today JOIN plants ON soil_status_today.id = plants.id
-> WHERE (num_events/max_samples_today(plants.id)) > 0.50 AND soil_status = 1;
+----+------------------+----------+-------------+------------+--------------------+
| id | name | location | soil_status | num_events | percent_occurrence |
+----+------------------+----------+-------------+------------+--------------------+
| 1 | Jerusalem Cherry | deck | DRY | 10 | 1.0000 |
+----+------------------+----------+-------------+------------+--------------------+
1 row in set (0.01 sec)