第1节数据处理之查询教学课件.ppt

The GROUP BY Clause You can use the GROUP BY clause to divide the rows in a table into groups. You can then use the group functions to return summary information for each group. In the syntax: group_by_expression specifies columns whose values determine the basis for grouping rows Guidelines If you include a group function in a SELECT clause, you cannot select individual results as well, unless the individual column appears in the GROUP BY clause. You receive an error message if you fail to include the column list in the GROUP BY clause. Using a WHERE clause, you can exclude rows before dividing them into groups. You must include the columns in the GROUP BY clause. You cannot use a column alias in the GROUP BY clause. By default, rows are sorted by ascending order of the columns included in the GROUP BY list. You can override this by using the ORDER BY clause. The GROUP BY Clause (continued) When using the GROUP BY clause, make sure that all columns in the SELECT list that are not group functions are included in the GROUP BY clause. The example on the slide displays the department number and the average salary for each department. Here is how this SELECT statement, containing a GROUP BY clause, is evaluated: The SELECT clause specifies the columns to be retrieved: Department number column in the EMPLOYEES table The average of all the salaries in the group you specified in the GROUP BY clause The FROM clause specifies the tables that the database must access: the EMPLOYEES table. The WHERE clause specifies the rows to be retrieved. Since there is no WHERE clause, all rows are retrieved by default. The GROUP BY clause specifies how the rows should be grouped. The rows are being grouped by department number, so the AVG function that is being applied to the salary column will calculate the average salary for each department. Instructor Note Group results are sorted implicitly, on the grouping column. You can use ORDER BY to specify a different sort order,