oracle分组函数.pptVIP

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 the default and is very rarely specified. Group Functions You can use AVG, SUM, MIN, and MAX functions against columns that can store numeric data. The example on the slide displays the average, highest, lowest, and sum of monthly salaries for all sales representatives. Group Functions (continued) You can use the MAX and MIN functions for any data type. The slide example displays the most junior and most senior employee. The following example displays the employee last name that is first and the employee last name that is the last in an alphabetized list of all employees. SELECT MIN(last_name), MAX(last_name) FROM employees; Note: AVG, SUM, VARIANCE, and STDDEV functions can be used only with numeric data types. The COUNT Function The COUNT function has three formats: COUNT(*) COUNT(expr) COUNT(DISTINCT expr) COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT statement, including duplicate rows and rows containing null values in any of the columns. If a WHERE clause is included in the SELECT statement, COUNT(*) returns the number of rows that satisfies the condition in the WHERE clause. In contrast, COUNT(expr) returns the number of non-null values in the column identified by expr. COUNT(DISTINCT expr) returns the nu