InterBase Quick Start: Part IV - Using Aggregate Functions
SQL provides aggregate functions that calculate a single value from a group of values. A group of values is all the data in a particular column for a given set of rows. You can use aggregate functions in a
SELECT clause or anywhere in a
SELECT statement where you specify a value.
The following table lists the aggregate functions that InterBase supports:
|What It Does
Returns the average value for a group of values.
Counts the number of values for a group of values.
Returns the minimum value in a group of values.
Returns the sumof numeric values in a group of values.
- Enter the following statement to find out how many different job codes are there in the
SELECT COUNT(job_code) FROM Job
- However, this is not what you want, because the query included duplicate job codes in the count. To count only the unique job codes, use the DISTINCT keyword as follows:
SELECT COUNT(DISTINCT job_code) FROM Job
You can see that there are 14 different job codes.
- Enter the following to retrieve the average budget of departments from the
SELECT AVG(budget) FROM Department
The result is
- A single
SELECTcan retrieve multiple aggregate functions. Enter the following statement to retrieve the number of employees, the earliest hire date, and the total salary paid to all employees:
SELECT COUNT(emp_no), MIN(hire_date), SUM(salary) FROM Employee
If a value involved in an aggregate calculation is
NULL or unknown, the function ignores the entire row to avoid wrong results. For example, when calculating an average over fifty rows, if ten rows contain a
NULL value, then the average is taken over forty values, not fifty.
To confirm that aggregate functions ignore
NULL rows, perform the following test:
- Retreive all the rows in the
SELECT dept_no, mngr_no FROM Department
There are 21 rows, and four of them have
NULLvalues in the
- Now count the rows in the
SELECT COUNT(mngr_no) FROM Department
The result is 17, not 21.
COUNTdoes not count the rows with