Aggregate Functions
To summarize all the data in a table, you create an aggregate query that involves a function such as SUM( ) or AVG( ). When you run the query, the result set contains a single row with the summary information. For example, you can calculate the total price of all books in the titles table by creating a query that sums the contents of the price column. The resulting query output might look like this:
.gif)
The corresponding SQL statement might look like this:
SELECT SUM(price) total_price
FROM titles
You can use the following aggregate functions :
| Aggregate function | Description |
|---|---|
| AVG(expr) | Average of the values in a column. The column can contain only numeric data. |
| COUNT(expr), COUNT(*) | A count of the values in a column (if you specify a column name as expr) or of all rows in a table or group (if you specify *). COUNT(expr) ignores null values, but COUNT(*) includes them in the count. |
| MAX(expr) | Highest value in a column (last value alphabetically for text data types). Ignores null values. |
| MIN(expr) | Lowest value in a column (first value alphabetically for text data types). Ignores null values. |
| SUM(expr) | Total of values in a column. The column can contain only numeric data. |
When you use an aggregate function, by default the summary information includes all specified rows. In some instances, a result set includes non-unique rows. You can filter out non-unique rows by using the DISTINCT option of an aggregate function.
You can combine aggregate functions with other expressions to calculate other summary values. For details, see Summarizing Values Using Custom Expressions.