Aggregating Query Results

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)