Using Aggregate Functions
Retrieving Data with Queries
Querying and Manipulating Data 2-23
Using Aggregate Functions
Aggregate functions operate on groups of rows, or an entire table or view. By their
nature, these functions provide statistical results for sets, and include average (
AVG
),
count (
COUNT
), maximum (
MAX
), minimum (
MIN
), standard deviation (
STDEV
), sum
(
SUM
), and so on.
Aggregate functions are especially powerful when used it in combination with the
GROUP BY
clause, where a query returns a list that is grouped by one or more
columns, with a distinct result for each of the groupings.
You can also use the HAVING clause, which specifies that a query should only return
rows where aggregate values meet the specified conditions.
COUNT
function and the
GROUP BY
clause
to determine how many people report to each manager. Note that the wildcard,
*
, is
used to denote the counting of an entire record.
Example 237 Counting the Number of Rows That Satisfy an Expression
SELECT manager_id "Manager",
COUNT(*) "Number of Reports"
FROM employees
GROUP BY manager_id;
The results of the query appear.
Manager Number of Reports
--------- -----------------
1
100 14
123 8
...
19 rows selected
Your result shows how many people report to each manager. Note that one person
does not report to anyone; if you examine the data, you will see that Steven King does
not have a supervisor.
COUNT
function with a
DISTINCT
option to determine how many distinct values and are in a data set. Here, you will
count the number of departments that have employees.
Example 238 Counting a Number of Distinct Values in a Set
SELECT COUNT(DISTINCT department_id) "Number of Departments"
FROM employees;
The results of the query appear.
Number of Departments
---------------------
11
Your result shows that
11
departments have employees. If you look at the
departments
table, you will note that it lists
27
departments.
See Also:
Oracle Database SQL Language Reference for data type conversion
functions
