Chapter 05-Reporting Aggregated data Using the Group Functions - 01
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 by using the GROUP BY clause
- Include or exclude grouped rows by using the HAVING clause
Lesson Agenda
- Groups functions:
- -Types and syntax
- -Use AVG,SUM,MIN,MAX,COUNT
- -Use DISTINCT keyword within group of functions
- -NULL values in a group function
- Grouping rows:
- -GROUP BY clause
- -HAVING clause
- Nesting group functions
What are group functions?
Group functions operate on sets of rows to give one result per group.
Types of Group Functions
- AVG
- COUNT
- MAX
- MIN
- STDDEV(计算标准偏差)
- SUM
- VARIANCE(计算方差)
Group Functions:Syntax
SELECT group_function(column),... FROM table [WHERE condition] [ORDER BY column]
注意要点:
(1)、组函数不包含NULL值,如果遇到NULL值,会主动放弃掉NULL值部分;如果想将NULL值作为计算元素在内,可以使用NVL函数,进行转换一下.
(2)、如果组函数操作的column中,存在重复行,可以使用keyword DISTICT,过滤掉重复行;缺省情况下Group Function是按照ALL来进行计算的.
Using the AVG and SUM Functions
You can use AVG and SUM for numeric data
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> SELECT AVG(salary),MAX(salary),MIN(SALARY),SUM(SALARY) FROM employees WHERE job_id LIKE '%REP%'; AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY) ----------- ----------- ----------- ----------- 8272.72727 11500 6000 273000
Using the MIN and MAX Functions
You can use MIN and MAX for numeric,character,and date data types.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> SELECT MIN(hire_date),MAX(hire_date) FROM employees; MIN(HIRE_ MAX(HIRE_ --------- --------- 13-JAN-01 21-APR-08
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> SELECT MIN(last_name),MAX(last_name) FROM employees; MIN(LAST_NAME) MAX(LAST_NAME) ------------------------- ------------------------- Abel Zlotkey
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> SELECT MIN(salary),MAX(salary) FROM employees; MIN(SALARY) MAX(SALARY) ----------- ----------- 2100 24000
Using the COUNT Function
- COUNT(*) return the number of rows in a table:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> SELECT COUNT(*) FROM employees; COUNT(*) ---------- 107
- COUNT(expr) returns the number of rows with non-null values for expr:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> SELECT count(commission_pct) FROM employees WHERE department_id = 80; COUNT(COMMISSION_PCT) --------------------- 34
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> SELECT COUNT(commission_pct) FROM employees; COUNT(COMMISSION_PCT) --------------------- 35 SQL> SELECT COUNT(NVL(commission_pct,0)) FROM employees; COUNT(NVL(COMMISSION_PCT,0)) ---------------------------- 107
Using the DISTINCT Keyword
- COUNT(DISTINCT expr) returns the number of distict non-null values of expr.
- TO display the number of distinct department values in ther EMPLOYEES table:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> SELECT COUNT(department_id) FROM employees; COUNT(DEPARTMENT_ID) -------------------- 106 SQL> SELECT COUNT(DISTINCT department_id) FROM employees; COUNT(DISTINCTDEPARTMENT_ID) ---------------------------- 11 SQL> SELECT COUNT(ALL department_id) FROM employees; COUNT(ALLDEPARTMENT_ID) ----------------------- 106 SQL> SELECT COUNT(NVL(department_id,0)) FROM employees; COUNT(NVL(DEPARTMENT_ID,0)) --------------------------- 107
Group Functions and Null Values
- Group functions ignore null values in the column:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> SELECT AVG(commission_pct) FROM employees; AVG(COMMISSION_PCT) ------------------- .222857143
- The NVL function forces group functions to include null values:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> SELECT AVG(NVL(commission_pct,0)) FROM employees; AVG(NVL(COMMISSION_PCT,0)) -------------------------- .072897196
Creating Groups of Data
Creating Groups of Data、GROUP BY Clause Syntax:
SELECT column,group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column]
注意:
(1)、group_function(column)与group_by_expression是有关联的.怎么个关联法?
(2)、column一定要包含在group by clause
(3)、如果where clause和group by clause同时存在的话,oracle server一定是先执行where clause.
(4)、在where clause和group by clause中是不能使用alias的.
You can divide rows in a table into smaller groups by using the GROUP BY clause.
Using the GROUP BY Clause
All columns in the SELECT list that are not in group functions must be in the GROUP BY clause
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SQL> SELECT NVL(department_id,999) deptid,ROUND(AVG(salary),2) AvgSalary FROM employees GROUP BY department_id ORDER BY department_id; DEPTID AVGSALARY ---------- ---------- 10 4400 20 9500 30 4150 40 6500 50 3475.56 60 5760 70 10000 80 8955.88 90 19333.33 100 8601.33 110 10154 DEPTID AVGSALARY ---------- ---------- 999 7000 12 rows selected.