Chapter 5 ■ MySQL priMer
There are additional clauses you can use including the GROUP BY clause, which is used for grouping
rows for aggregation or counting, and the ORDER BY clause, which is used to order the result set. Let’s take a
quick look at each starting with aggregation.
Suppose you wanted to average the sensor values read in the table for each sensor. In this case, we have
a table that contains sensor readings over time for a variety of sensors. While the example contains only four
rows (and thus may not be statistically informative), the example demonstrates the concept of aggregation quite
plainly, as shown in Listing 5-5. Notice what we receive is simply the average of the four sensor values read.
Listing 5-5. GROUP BY Example
mysql> SELECT plant_name, sensor_value FROM plant_monitoring.plants WHERE plant_name =
'fern on deck';
+--------------+--------------+
| plant_name | sensor_value |
+--------------+--------------+
| fern on deck | 0.43 |
| fern on deck | 0.51 |
| fern on deck | 0.477 |
| fern on deck | 0.73 |
+--------------+--------------+
4 rows in set (0.00 sec)
mysql> SELECT plant_name, AVG(sensor_value) as avg_value FROM plant_monitoring.plants
WHERE plant_name = 'fern on deck' GROUP BY plant_name;
+--------------+-------------------+
| plant_name | avg_value |
+--------------+-------------------+
| fern on deck | 0.536750003695488 |
+--------------+-------------------+
1 row in set (0.00 sec)
Notice I specified the average function, AVG(), in the column list and passed in the name of the
column I wanted to average. There are many such functions available in MySQL to perform some powerful
calculations. Clearly, this is another example of how much power exists in the database server that would
require many more resources on a typical lightweight sensor or aggregator node in the network.
Notice also that I renamed the column with the average with the AS keyword. You can use this to
rename any column specified, which changes the name in the result set, as you can see in the listing.
Another use of the GROUP BY clause is counting. In this case, we replaced AVG() with COUNT() and
received the number of rows matching the WHERE clause. More specifically, we want to know how many
sensor values were stored for each plant.
mysql> SELECT plant_name, COUNT(sensor_value) as num_values FROM plant_monitoring.plants
GROUP BY plant_name;
+------------------------+------------+
| plant_name | num_values |
+------------------------+------------+
| fern in den | 1 |
| fern on deck | 4 |
| flowers in bedroom1 | 1 |
| weird plant in kitchen | 1 |
+------------------------+------------+
4 rows in set (0.00 sec)