Database Reference
In-Depth Information
6.5.1 Aggregate Functions
Aggregate built-in functions perform some calculations on a set of rows. hey are used with
SELECT, GROUP BY, or HAVING statements. he commonly used aggregate functions are
COUNT, AVG, SUM, MAX, and MIN. Table 6.5 lists the commonly used aggregate functions,
their usage, and related examples.
he aggregate functions in Table 6.5 are also scalar functions. hat is, they all return a single
value. hese built-in functions can be used in a SELECT clause and a WHERE clause. For
example, if you want to ind out which classroom has the largest capacity, try the query in Figure
6.45 that uses the MAX function in the WHERE clause.
As you can see, the classroom 103 has the largest capacity, which is 40. he next example
illustrates how a built-in function can be used in a SELECT clause. In Figure 6.46, the COUNT
function is used to count how many students are currently enrolled in the Database class.
he output of the query shows the number of students enrolled in the Database class.
When built-in functions are used in a SELECT clause, which includes columns and functions,
the set of values returned from a column usually do not match the single value returned by a func-
tion. For example, the following query will generate an error since four rows will be selected from
ClassroomID and only a single value will be returned from MIN(Capacity).
SELECT ClassroomID, MIN(Capacity)
FROM CLASSROOM
Table 6.5
Built-In Aggregate Functions
Function
Usage
Example
COUNT
Counts how many nonnull rows
are in a given column
SELECT COUNT(GPA)
FROM ENROLLMENT
SUM
Sums the values in a given
numerical column
SELECT SUM(GPA)
FROM ENROLLMENT
MAX
Finds the maximum value in a
given numerical column
SELECT MAX(GPA)
FROM ENROLLMENT
MIN
Finds the minimum value in a
given numerical column
SELECT MIN(GPA)
FROM ENROLLMENT
AVG
Calculates the average for the
values in a given numerical column
SELECT AVG(GPA)
FROM ENROLLMENT
Figure 6.45
Use built-in function in subquery.
