oracle查询基础6.ppt

[Title of the course] * Copyright ? 2004-2005 NameOfTheOrganization. All rights reserved. * In this lesson you learn how to: ? Group data to obtain the following: - Subtotal values by using the ROLLUP operator - Cross-tabulation values by using the CUBE operator ? Use the GROUPING function to identify the level of aggregation in the result set produced by a ROLLUP or CUBE operator ? Use GROUPING SETS to produce a single result set that is equivalent to a UNION ALL approach [Title of the course] * Copyright ? 2004-2005 NameOfTheOrganization. All rights reserved. * [Title of the course] * Copyright ? 2004-2005 NameOfTheOrganization. All rights reserved. * [Title of the course] * Copyright ? 2004-2005 NameOfTheOrganization. All rights reserved. * [Title of the course] * Copyright ? 2004-2005 NameOfTheOrganization. All rights reserved. * You can use the GROUP BY clause to divide the rows in a table into groups. You can then use group functions to return summary information for each group. Group functions can appear in select lists and in ORDER BY and HAVING clauses. The Oracle server applies the group functions to each group of rows and returns a single result row for each group. Types of group functions: Each of the group functions AVG, SUM, MAX, MIN, COUNT, STDDEV, and VARIANCE accept one argument. The functions AVG, SUM, STDDEV, and VARIANCE operate only on numeric values. MAX and MIN can operate on numeric, character, or date data values. COUNT returns the number of non-null rows for the given expression. The example on the slide calculates the average salary, standard deviation on the salary, number of employees earning a commission, and the maximum hire date for those employees whose JOB_ID begins with SA. Guidelines for Using Group Functions ? The data types for the arguments can be CHAR, VARCHAR2, NUMBER, or DATE. ? All group functions except COUNT(*) ignore null values. To substitute a value for null values, use the NVL function. COUNT returns either a n