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.
| num |
|---|
| 2 |
| 2 |
| 2 |
| 3 |
| 3 |
| 4 |
| (NULL) |
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 |