Đang chuẩn bị nút TẢI XUỐNG, xin hãy chờ
Tải xuống
After completing this lesson, you should be able to do the following: Identify the available group functions Describe the use of group functions Group data using the GROUP BY clause Include or exclude grouped rows by using the HAVING clause | Aggregating Data Using Group Functions Schedule: Timing Topic 35 minutes Lecture 40 minutes Practice 75 minutes Total Objectives After completing this lesson, you should be able to do the following: Identify the available group functions Describe the use of group functions Group data using the GROUP BY clause Include or exclude grouped rows by using the HAVING clause Lesson Aim This lesson further addresses functions. It focuses on obtaining summary information, such as averages, for groups of rows. It discusses how to group rows in a table into smaller sets and how to specify search criteria for groups of rows. What Are Group Functions? Group functions operate on sets of rows to give one result per group. EMPLOYEES The maximum salary in the EMPLOYEES table. Group Functions Unlike single-row functions, group functions operate on sets of rows to give one result per group. These sets may be the whole table or the table split into groups. Types of Group Functions AVG COUNT MAX MIN . | Aggregating Data Using Group Functions Schedule: Timing Topic 35 minutes Lecture 40 minutes Practice 75 minutes Total Objectives After completing this lesson, you should be able to do the following: Identify the available group functions Describe the use of group functions Group data using the GROUP BY clause Include or exclude grouped rows by using the HAVING clause Lesson Aim This lesson further addresses functions. It focuses on obtaining summary information, such as averages, for groups of rows. It discusses how to group rows in a table into smaller sets and how to specify search criteria for groups of rows. What Are Group Functions? Group functions operate on sets of rows to give one result per group. EMPLOYEES The maximum salary in the EMPLOYEES table. Group Functions Unlike single-row functions, group functions operate on sets of rows to give one result per group. These sets may be the whole table or the table split into groups. Types of Group Functions AVG COUNT MAX MIN STDDEV SUM VARIANCE Group Functions (continued) Each of the functions accepts an argument. The following table identifies the options that you can use in the syntax: SELECT [column,] group_function(column), . FROM table [WHERE condition] [GROUP BY column] [ORDER BY column]; Group Functions Syntax Guidelines for Using Group Functions DISTINCT makes the function consider only nonduplicate values; ALL makes it consider every value including duplicates. The default is ALL and therefore does not need to be specified. The data types for the functions with an expr argument may be CHAR, VARCHAR2, NUMBER, or DATE. All group functions ignore null values. To substitute a value for null values, use the NVL, NVL2, or COALESCE functions. The Oracle server implicitly sorts the result set in ascending order when using a GROUP BY clause. To override this default ordering, DESC can be used in an ORDER BY clause. Instructor Note Stress the use of DISTINCT and group functions ignoring null values. ALL is .
