tailieunhanh - Enhancements to the GROUP BY Clause

After completing this lesson, you should be able to do the following: Use the ROLLUP operation to produce subtotal values Use the CUBE operation to produce cross-tabulation values Use the GROUPING function to identify the row values created by ROLLUP or CUBE Use GROUPING SETS to produce a single result set | Enhancements to the GROUP BY Clause Schedule: Timing Topic 45 minutes Lecture 30 minutes Practice 75 minutes Total Objectives After completing this lesson, you should be able to do the following: Use the ROLLUP operation to produce subtotal values Use the CUBE operation to produce cross-tabulation values Use the GROUPING function to identify the row values created by ROLLUP or CUBE Use GROUPING SETS to produce a single result set Lesson Aim In this lesson you learn how to: Group data for obtaining 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 results 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. Review of Group Functions Group functions operate on sets of rows to give one result per group. SELECT [column,] group_function(column). . . FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column]; SELECT AVG(salary), STDDEV(salary), COUNT(commission_pct),MAX(hire_date) FROM employees WHERE job_id LIKE 'SA%'; Example: Group Functions 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. 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 nonnull rows for the given expression. The example in the slide calculates the average salary, standard deviation on the salary, number of employees earning a . | Enhancements to the GROUP BY Clause Schedule: Timing Topic 45 minutes Lecture 30 minutes Practice 75 minutes Total Objectives After completing this lesson, you should be able to do the following: Use the ROLLUP operation to produce subtotal values Use the CUBE operation to produce cross-tabulation values Use the GROUPING function to identify the row values created by ROLLUP or CUBE Use GROUPING SETS to produce a single result set Lesson Aim In this lesson you learn how to: Group data for obtaining 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 results 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. Review of Group Functions Group functions operate on sets of rows to give one result per group. SELECT [column,] group_function(column). . . FROM table [WHERE condition] .