MySQL Data Aggregation Functions

Sometimes when working with the data stored in a MySQL database table, we are interested not in the data itself, but in statistics about that data. For example, we may not be concerned about the specific content in each row, we may want to know how many rows are in a table. Alternatively, we may need to find the average of all the values in a particular table column. Information of this type can be obtained using a collection of built-in MySQL aggregate functions and these functions are the topic of this chapter.

MySQL supports the following aggregate functions:

For the purposes of demonstrating the aggregate functions in action we will use a table with the following data:

+-----------+----------------------------+-----------------------------+------------+
| prod_code | prod_name                  | prod_desc                   | prod_price |
+-----------+----------------------------+-----------------------------+------------+
|         1 | WildTech 250Gb 1700        | SATA Disk Drive             |        120 |
|         2 | Moto Razr                  | Mobile Phone                |        200 |
|         3 | Microsoft 10-20 Keyboard   | Ergonmoc Keyboard           |         49 |
|         4 | EasyTech Mouse 7632        | Cordless Mouse              |         49 |
|         5 | Dell XPS 400               | Desktop PC                  |        999 |
|         6 | Buffalo AirStation Turbo G | Wireless Ethernet Bridge    |         60 |
|         7 | Apple iPod Touch           | Portable Music/Movie Player |        199 |
|         8 | Apple iPhone 8Gb           | Smart Phone                 |        399 |
+-----------+----------------------------+-----------------------------+------------+

Feel free to create a similar table, or just follow along with the examples to get an idea of how to use these function.

The AVG() function adds together all the values for a specified column in a SELECT statement and divides it by the number of rows to arrive at an average value. The result can then be assigned to an alias using the AS clause. For example, to find the average price of the products in our database, and assign the result to an alias named price_avg:

mysql> SELECT AVG(prod_price) AS price_ag FROM products;
+----------+
| price_ag |
+----------+
|  259.375 |
+----------+
1 row in set (0.00 sec)

We can also be selective about the rows used in the average calculation by using the WHERE clause:

mysql> SELECT AVG(prod_price) AS price_avg FROM products WHERE prod_price BETWEEN 10 and 199;
+-----------+
| price_avg |
+-----------+
|     95.4  |
+-----------+
1 row in set (0.00 sec)

The MySQL COUNT() function adds the number of rows that match the filter criteria specified in a SELECT statement. For example, to count the number of rows with a price in our sample table:

mysql> SELECT COUNT(*) FROM products;
+----------+
| price_ag |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

Similarly, we can restrict our criteria to list the number of products beneath a specific price threshold:

mysql> SELECT COUNT(prod_price) AS low_price_items FROM products WHERE prod_price < 200;
+-----------------+
| low_price_items |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)