Database Reference
In-Depth Information
11.2
Types of Group Functions
Group functions are different from single-row functions in that group func-
tions work on data in sets, or groups of rows, rather than on data in a single
row. For example, you can use a group function to add up all payments
made in one month. You can combine single-row and group functions to
further refine the results of the GROUP BY clause.
There are many group functions available to use with the GROUP BY
clause. Functions operating on groups of rows fall into the following cate-
gories:
Aggregate Functions
. Functions that summarize data into a single
value, such as the MAX function, returning the highest value among
the group of rows.
Statistical Functions
. These functions are essentially aggregation
functions in that they perform explicit calculations on specified
groups of rows. However, statistical functions are appropriate to
both aggregation and analytics.
Analytic Functions
. Functions that summarize data into multiple
values based on a sliding window of rows using an analytic clause.
These structures are used most frequently in data warehousing to
analyze historical trends in data. For example, the statistical STD-
DEV function can be used as an analytic function that returns stan-
dard deviations over groups of rows.
SPREADSHEET Clause Functions
. SPREADSHEET clause func-
tions enhance the SPREADSHEET clause. These functions are cov-
ered later in this chapter in the section on the SPREADSHEET
clause.
Let's begin with aggregate functions.
11.2.1
Aggregate Functions
An aggregate function applies an operation to a group of rows returning a
single value. A simple example of an aggregate function is in the use of the
SUM function as shown following. See the result in Figure 11.2.
SELECT SUM(AMOUNT_CHARGED), SUM(AMOUNT_PAID) FROM STUDIOTIME;
