Using Aggregate Functions
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 |
- Table 6: Aggregate Functions Supported by InterBase Function
Practicing with Aggregate Functions
- 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.
- 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.
- 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
- 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.
- 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.
- 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.