A SELECT statement can produce a list of rows that match a given set of conditions. The list provides the details about the selected rows, but if you want to know about the overall characteristics of the rows, you'll be more interested in getting a summary instead. When that's your goal, use aggregate functions to calculate summary values, possibly combined with a GROUP BY clause to arrange the selected rows into groups so that you can get summaries for each group. Grouping can be based on the values in one or more columns of the selected rows. For example, the Country table indicates which continent each country is part of, so you can group the records by continent and calculate the average population of countries in each continent: SELECT Continent, AVG(Population) FROM Country GROUP BY Continent; Functions such as AVG() that calculate summary values for groups are known as "aggregate" functions because they're based on aggregates or groups of values. There are several types of aggregate functions. Those discussed here are as follows:
Aggregate functions may be used with or without a GROUP BY clause that places rows into groups. Without a GROUP BY clause, an aggregate function calculates a summary value based on the entire set of selected rows. (That is, MySQL treats all the rows as a single group.) With a GROUP BY clause, an aggregate function calculates a summary value for each group. For example, if a WHERE clause selects 20 rows and the GROUP BY clause arranges them into four groups of five rows each, a summary function produces a value for each of the four groups. This section describes the aggregate functions available to you. Section 9.5, "Grouping Results," shows how to use GROUP BY to group rows appropriately for the type of summary you want to produce. 9.4.1. The MIN() and MAX() Aggregate FunctionsMIN() and MAX() are comparison functions. They return smallest or largest numeric values, lexically first or last string values, and earliest or latest temporal values. The following queries determine the smallest and largest country populations and the lexically first and last country names: mysql> SELECT MIN(Population), MAX(Population) FROM Country; +-----------------+-----------------+ | MIN(Population) | MAX(Population) | +-----------------+-----------------+ | 0 | 1277558000 | +-----------------+-----------------+ mysql> SELECT MIN(Name), MAX(Name) FROM Country; +-------------+-----------+ | MIN(Name) | MAX(Name) | +-------------+-----------+ | Afghanistan | Zimbabwe | +-------------+-----------+ For string values, the behavior of MIN() and MAX() depends on whether the strings are non-binary or binary. Consider a table t that contains the following string values: mysql> SELECT name FROM t; +--------+ | name | +--------+ | Calvin | | alex | +--------+ If the name column has a non-binary string data type such as CHAR or TEXT, MAX(name) determines which value is greatest based on the string collation. For the default case-insensitive collation of latin1_swedish_ci, MAX() returns 'Calvin' because 'c' is greater than 'a': mysql> SELECT MAX(name) FROM t; +-----------+ | MAX(name) | +-----------+ | Calvin | +-----------+ If the name column has a binary string data type such as BINARY or BLOB, its values are compared using the numeric values of the bytes in the strings. If 'C' has a smaller numeric value 'a' (as is true if characters are stored using ASCII codes), MAX(name) returns 'alex': mysql> ALTER TABLE t MODIFY name BINARY(20); mysql> SELECT MAX(name) FROM t; +-----------+ | MAX(name) | +-----------+ | alex | +-----------+ MIN() and MAX() ignore NULL values. 9.4.2. The SUM() and AVG() Aggregate FunctionsThe SUM() and AVG() functions calculate sums and averages. For example, the Country table in the world database contains a Population column, so you can calculate the total world population and the average population per country like this: mysql> SELECT SUM(Population), AVG(Population) FROM Country; +-----------------+-----------------+ | SUM(Population) | AVG(Population) | +-----------------+-----------------+ | 6078749450 | 25434098.1172 | +-----------------+-----------------+ SUM() and AVG() are most commonly used with numeric values. If you use them with other types of values, those values are subject to numeric conversion, which might not produce a sensible result. SUM() and AVG() ignore NULL values. 9.4.3. The COUNT() Aggregate FunctionThe COUNT() function can be used in several ways to count either rows or values. To illustrate, the examples here use the following table that has several rows containing various combinations of NULL and non-NULL values: mysql> SELECT i, j FROM t; +------+------+ | i | j | +------+------+ | 1 | NULL | | NULL | 2 | | 1 | 1 | | 1 | 1 | | 1 | 3 | | NULL | NULL | | 1 | NULL | +------+------+ COUNT() may be used as follows:
9.4.4. The GROUP_CONCAT() FunctionThe purpose of the GROUP_CONCAT() function is to concatenate column values into a single string. This is useful if you would otherwise perform a lookup of many rows and then concatenate them on the client end. For example, the following query displays the languages spoken in Thailand, one per line: mysql> SELECT Language -> FROM CountryLanguage WHERE CountryCode = 'THA'; +----------+ | Language | +----------+ | Chinese | | Khmer | | Kuy | | Lao | | Malay | | Thai | +----------+ To concatenate the values into a single string, use GROUP_CONCAT(): mysql> SELECT GROUP_CONCAT(Language) -> AS Languages -> FROM CountryLanguage WHERE CountryCode = 'THA'; +----------------------------------+ | Languages | +----------------------------------+ | Chinese,Khmer,Kuy,Lao,Malay,Thai | +----------------------------------+ GROUP_CONCAT() supports several modifiers:
GROUP_CONCAT() ignores NULL values. 9.4.5. Aggregation for NULL Values or Empty SetsIn general, aggregate functions ignore NULL values. The exception is COUNT(), which behaves as follows:
A SELECT statement might produce an empty result set if the table is empty or the WHERE clause selects no rows from it. If the set of values passed to an aggregate function is empty, the function computes the most sensible value. For COUNT(), the result is zero. But functions such as MIN(), MAX(), SUM(), AVG(), and GROUP_CONCAT() return NULL. They also return NULL if a non-empty result contains only NULL values. These behaviors occur because there is no way for such functions to compute results without at least one non-NULL input value. |