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.
Bạn đang xem 3. - OCA OCP ORACLE DATABASE 11G A LL IN ONE EXAM GUIDE P51 PPS