EXECUTING THIS STATEMENT SHOWS THAT THE AVERAGE LENGTH OF ALL THE COUNTRY NAMES IN THE COUNTRIES TABLE IS EIGHT CHARACTERS

3. Executing this statement shows that the average length of all the country

names in the COUNTRIES table is eight characters.

Group Data Using the GROUP BY Clause

The group functions discussed earlier use groups of rows making up the entire table.

This section explores partitioning a set of data into groups using the GROUP BY

clause. Group functions may be applied to these subsets or clusters of rows.

Creating Groups of Data

A table has at least one column and zero or more rows of data. In many tables data

requires analysis to transform it into useful information. It is a common reporting

requirement to calculate statistics from a set of data divided into groups using different

attributes. Previous examples using group functions operated against all the rows in a

table. The entire table was treated as one large group. Groups of data within a set are

created by associating rows with common attributes with each other. Thereafter, group

functions can execute against each of these groups. Groups of data include entire rows

and not specific columns.

Consider the EMPLOYEES table. It comprises 11 columns and 107 rows. You could

create groups of rows that share a common DEPARTMENT_ID value. The SUM function

may then be used to create salary totals per department. Another possible set of groups

may share common JOB_ID column values. The AVG group function may then be used

to identify the average salary paid to employees in different jobs.

A group is defined as a subset of the entire dataset sharing one or more common

attributes. These attributes are typically column values but may also be expressions.

The number of groups created depends on the distinct values present in the common

attribute.

As Figure 11-2 shows, there are 12 unique DEPARTMENT_ID values in the

EMPLOYEES table. If rows are grouped using common DEPARTMENT_ID values,

there will be 12 groups. If a group function is executed against these groups, there

will be 12 values returned, as it will execute once for each group.