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 data in a particular column for a given set of rows, such as the job code listed in all rows of the JOB table. Aggregate functions may be used in a SELECT clause, or anywhere a value is used in a SELECT statement.
The following table lists the aggregate functions supported by InterBase:
|
Function |
What It Does |
|---|---|
| AVG(value) |
Returns the average value for a group of rows. |
|
COUNT(value) |
Counts the number of rows that satisfy the WHERE clause. |
|
MIN(value) |
Returns the minimum value in a group of rows. |
|
SUM(value) |
Adds numeric values in a group of rows. |
Aggregate Functions Supported by InterBase Function
Practicing with Aggregate Functions
1. Suppose you want to know how many different job codes are in the Job table. Enter the following statement:
SELECT COUNT(job_code) FROM Job
- The result count is 31.
- However, this is not what you want, because the query included duplicate job codes in the count.
2. To count only the unique job codes, use the DISTINCT keyword as follows:
SELECT COUNT(DISTINCT job_code) FROM Job
- This produces the correct result of 14.
3. Enter the following to retrieve the average budget of departments from the Department table:
SELECT AVG(budget) FROM Department
- The result average is 648095.23.
4. 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
- The result sum is: a count of 42, a minimum hire date of 12/28/1988, and a salary sum of 37860791.52.
- Note: The value in the sum column may vary, depending on which exercises you have done and whether you have done some of them more than once.
- Important: If a value involved in an aggregate calculation is NULL or unknown, the function ignores the entire row to prevent 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.
5. To see for yourself that aggregate functions ignore NULL rows, perform the following test: first, look at all the rows in the Department table:
SELECT dept_no, mngr_no FROM Department
- Notice that there are 21 rows, but four of them have NULLs in the mngr_no column.
6. Now count the rows in mngr_no:
SELECT COUNT(mngr_no) FROM Department
- The result is 17, not 21. COUNT did not count the NULL rows.