A SELECT statement can produce a list of rows that match a given set of conditions. This list provides the details about the selected rows. However, to know about the overall characteristics of the rows, a summary can be more valuable. Aggregate functions (also known as Summary functions) perform summary operations on a set of values, such as counting, averaging, or finding minimum or maximum values. They calculate a single value based on a group of values. However, the resulting value is based only on non NULL values from the selected rows (with the exception that COUNT(*) counts all rows).
Functions such as AVG() that calculate summary values for groups are known as “aggregate” functions because they are based on aggregates or groups of values. There are several aggregate functions. Some of the most common are as follows;
Aggregate Functions Definition
MIN() Find the smallest value
MAX() Find the largest value
SUM() Summarize numeric value totals
AVG() Summarize numeric value averages
STD() Returns the standard deviation
COUNT() Counts rows, non-null values, or the number of distinct values
GROUP_CONCAT() Concatenates a set of strings to produce a single string
All the functions can be used with the DISTINCT keyword (although it's useless for MAX() and MIN()). In the following example the COUNT(*) function is being used to get a count of all rows in the Country table of the world database;
mysql> SELECT COUNT(*) FROM Country;
+--------------+
| COUNT(*) |
+--------------+
| 239 |
+--------------+
1 row in set (#.## sec)
Now specifying a count of Capital yields a different result, due to the fact that not every country has a Capital associated with it. Therefore, the NULL values are not included in the count;
mysql> SELECT COUNT(Capital) FROM Country;
+-----------------------+
| COUNT(Capital) |
+-----------------------+
| 232 |
+-----------------------+
1 row in set (#.## sec)
Comments
Post a Comment