Creating Groups

Dividing Data into Groups

You might need to divide a table of information into groups to produce meaningful results when using group functions. For example, you might want to find the average salary for each department or the oldest hire in each job category.

GROUP BY Clause

You use the GROUP BY clause to organize rows in a table into groups. You can then use the group functions to return information for each group. For example, you can group data in the EMPLOYEES table by department number and then return the average salary for each department.

GROUP BY Syntax

You place the GROUP BY clause between the WHERE and ORDER BY clauses in a SQL statement. You specify the GROUP BY keyword followed by a column name or expression that indicates how you want to group the rows.

Guidelines

• The SELECT clause can contain only the column or columns that appear in the GROUP BY clause, in addition to any group function or functions.
• The GROUP BY clause must contain any column or columns that appear in the SELECT clause that are not listed in the group function.
• By default, rows are sorted in ascending order of the columns that are included in the GROUP BY list.
• The ORDER BY clause is optional. If used, it overrides the default sorting.