InterBase Quick Start: Part IV  Using Aggregate Functions
Go Up to InterBase Quick Start: Part IV  Retrieving Data
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:
Function  What It Does 

AVG(value)

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. 
Using Aggregate Functions
 Enter the following statement to find out how many different job codes are there in the
Job
table: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
Department
table:SELECT AVG(budget) FROM Department
The result is
648095.23
.  A single
SELECT
can 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
Aggregate Functions and NULL Values
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
Department
table:SELECT dept_no, mngr_no FROM Department
There are 21 rows, and four of them have
NULL
values in themngr_no
column.  Now count the rows in the
mngr_no
column:SELECT COUNT(mngr_no) FROM Department
The result is 17, not 21.
COUNT
does not count the rows withNULL
value.