An example can help to summarize the behavior of the aggregate
functions. Assume that the
testtable table has a single INTEGER
column that is named
num. The contents of this table are as
follows.
You can use aggregate functions to obtain information about the
num column
and the
testtable table. The following query uses the
AVG function
to obtain the average of all the non-NULL values in the
num column:
SELECT AVG(num) AS average_number FROM testtable;
The following table shows the result of this query.
| average_number |
| 2.66666666666667 |
You can use the other aggregate functions in SELECT statements
that are similar to the preceding example. If you enter a series of
SELECT statements that have different aggregate functions in the projection
list and do not include a WHERE clause, you receive the results that
the following table shows.
| Function |
Results |
|
Function |
Results |
| COUNT (*) |
7 |
|
MAX |
4 |
| COUNT (DISTINCT) |
3 |
|
MAX(DISTINCT) |
4 |
| COUNT (ALL num) |
6 |
|
MIN |
2 |
| COUNT ( num ) |
6 |
|
MIN(DISTINCT) |
2 |
| AVG |
2.66666666666667 |
|
RANGE |
2 |
| AVG (DISTINCT) |
3.00000000000000 |
|
SUM |
16 |
| STDEV |
0.74535599249993 |
|
SUM(DISTINCT) |
9 |
| VARIANCE |
0.55555555555556 |
|
|
|