Aggregate Functions
Aggregate Functions
- Aggregate functions are synonymous with column functions.
- A summary query uses at least on column function.
- AVG, SUM return numeric values.
- MIN, MAX, COUNT can return numeric, date, or string values
- All values are included in aggregate functions by default unless you specify the DISTINCT keyword
- Duplicate rows are excluded in all aggregate functions with the exception of COUNT(*)
- ***** IF YOU CODE AN AGGREGATE FUNCTION IN THE SELECT STATEMENT, YOU CANNOT ALSO INCLUDE NON-AGGREGATE FUNCTIONS IN THE SELECT STATEMENT UNLESS THOSE NON-AGGREGATE COLUMNS ARE INCLUDED IN A GROUP BY CLAUSE
Table 1. Aggregate Functions List
Aggregate Function |
Output data-type |
Result |
AVG([DISTINCT] column_values) |
numeric |
The average of the non-null columns in the expression |
SUM([DISTINCT] column_values) |
numeric |
The total of the non-null columns in the expression |
MIN([DISTINCT] column_values) |
numeric, date, string |
The lowest value off the non-null columns in the expression |
MAX([DISTINCT] column_values) |
numeric, date, string |
The highest value of the non-null columns in the expression |
COUNT([DISTINCT] column_values) |
numeric |
The number of the non-null columns in the expression |
COUNT(*) |
numeric |
The number of rows returned by the query |
Code Sample:
USE bike;
SELECT AVG(list_price), SUM(list_price), MIN(list_price),
MAX(list_price), COUNT(list_price), COUNT(*)
FROM product;
Output:
End-of-Chapter Survey
: How would you rate the overall quality of this chapter?- Very Low Quality
- Low Quality
- Moderate Quality
- High Quality
- Very High Quality