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)

The MAX() function returns data from the row in which the specified column contains the highest value. For example we can findthe most expensive product in our database table:

mysql> SELECT MAX(prod_price) AS max_price FROM products;
+-----------+
| max_price |
+-----------+
|       999 |
+-----------+
1 row in set (0.00 sec)

The MIN() function performs the opposite task to the MAX() function in that it returns data from the row containing the lowest value in the specified column. For example, to find the least expensive item in our table:

mysql> SELECT MIN(prod_price) AS min_price FROM products;
+-----------+
| max_price |
+-----------+
|        49 |
+-----------+
1 row in set (0.00 sec)

The SUM() function returns the total of all the values in a a specified column. Therefore, to get the total value of every item in the table:

mysql> SELECT SUM(prod_price) AS total_price FROM products;
+-----------+
| max_price |
+-----------+
|      2075 |
+-----------+
1 row in set (0.00 sec)

SELECT statements are not restricted to a single aggregate function. It is perfectly valid to include calls to multiple functions, for example:

mysql> SELECT MAX(prod_price) AS max_price, MIN(prod_price) AS max_price FROM products;
+-----------+-----------+
| max_price | max_price |
+-----------+-----------+
|       999 |        49 |
+-----------+-----------+
1 row in set (0.00 sec)