You can produce summary statistics for a table using functions on fields with the SQL statement.
For example, using the Employee table:
SELECT COUNT(Department), SUM(Age), MIN(JoinDate), MAX(JoinDate) FROM Employee;
produces a single row of summary information:
COUNT(Department) |
SUM(Age) |
MIN(JoinDate) |
MAX(JoinDate) |
5 |
147 |
1-Oct-1986 |
10-Mar-1992 |
You can use these functions in conjunction with the GROUP BY keywords to produce consolidated group statistics. For example:
SELECT Department, AVG(Age), COUNT(*) FROM Employee GROUP BY Department;
This time we can include Department in the select statement as this is the field we are grouping on. The results table lists, in each department, the average age and the total number of people.
Department |
AVG(Age) |
COUNT(*) |
ADMIN |
30 |
1 |
SALES |
25 |
2 |
TECH |
33.5 |
2 |
As normal select statements have an optional WHERE clause to specify which rows are included from the table, so GROUP statements have an optional HAVING clause.
For example, to show a list of departments with an average age over 30, use the following:
SELECT Department, AVG(Age), COUNT(*) FROM Employee GROUP BY Department HAVING AVG(Age) > 30;
Here’s a complete list of aggregate functions you can use:
Function |
Meaning |
COUNT |
Count the number of records |
SUM |
Total |
MAX |
Find the maximum value of field |
MIN |
Find the minimum value of field |
AVG |
Average |
VAR |
Sample variance |
VARP |
Population variance |
STDEV |
Sample standard deviation |
STDEVP |
Population standard deviation |
See also: