組函數：
組函數操作行集，給出每組的結果。組函數不象單行函數，組函數對行的集合進行操作，對每組給出一個結果。這些集合可能是整個表或者是表分成的組。
組函數與單行函數差別：
單行函數對查詢到每個結果集做處理，而組函數隻對分組資料做處理。
單行函數對每個結果集傳回一個結果，而組函數對每個分組傳回一個結果。
組函數的類型：
•AVG 平均值
• COUNT 計數
• MAX 最大值
• MIN 最小值
• SUM 合計
組函數的文法：
使用組函數的原則：
• 用于函數的參數的資料類型可以是 CHAR、VARCHAR2、NUMBER 或 DATE。
• 所有組函數忽略空值。為了用一個值代替空值，用 NVL、NVL2 或 COALESCE 函數。
組函數的使用：
1.使用 AVG 和 和 SUM
AVG(arg)函數：對分組資料做平均值運算。
arg:參數類型隻能是數字類型。
SUM(arg)函數：對分組資料求和。
arg:參數類型隻能是數字類型 。
例： 求雇員表中的的平均薪水與薪水總額。
SQL> select sum(e.salary) "薪水總額：",avg(e.salary) "平均薪水：" from employees e;
薪水總額： 平均薪水：
---------- ----------
681816 6432.22641
2.用 使用 MIN 和 和 MAX
MIN(arg)函數：求分組中最小資料。
arg:參數類型可以是字元、數字、日期。
MAX(arg)函數：求分組中最大資料。
arg:參數類型可以是字元、數字、日期。
例： 求雇員表中的最高薪水與最低薪水。
SQL> select min(e.salary) MIN_SALARY,max(e.salary) MAX_SALARY from employees e;
MIN_SALARY MAX_SALARY
---------- ----------
2100 24000
3.用 使用 COUNT 函數
COUNT 函數：傳回一個表中的行數。
COUNT 函數有三種格式：
• COUNT(*)
• COUNT(expr)
• COUNT(DISTINCT expr)
COUNT(*):
傳回表中滿足 SELECT 語句标準的行數，包括重複行，包括有空值列的行。如果WHERE 子句包括在 SELECT 語句中，COUNT(*) 傳回滿足 WHERE 子句條件的行數.
例：傳回查詢結果的總條數：
SQL> select count(*) from employees;
COUNT(*)
----------
106
COUNT(expr)
傳回在列中的由 expr 指定的非空值的數
例：顯示部門 80 中有傭金的雇員人數
SQL> select count(e.commission_pct) from employees e where e.department_id=80;
COUNT(E.COMMISSION_PCT)
-----------------------
33
COUNT(DISTINCT expr：
使用 DISTINCT 關鍵字禁止計算在一列中的重複值。
例：顯示 EMPLOYEES 表中不重複的部門數。
SQL> select count(e.department_id) from employees e;
COUNT(E.DEPARTMENT_ID)
----------------------
105
SQL> select count(distinct e.department_id) from employees e;
COUNT(DISTINCTE.DEPARTMENT_ID)
------------------------------
11
4.組函數和 Null ：
所有組函數忽略列中的空值。
在組函數中使用 NVL 函數來處理空值。
例：計算有傭金的員工的傭金平均值。
SQL> select avg(e.commission_pct) from employees e where e.commission_pct is not null;
AVG(E.COMMISSION_PCT)
---------------------
0.223529411764706
SQL> select avg(e.commission_pct) from employees e;
AVG(E.COMMISSION_PCT)
---------------------
0.223529411764706
例：計算所有員工的傭金的平均值。
SQL> select avg(nvl(e.commission_pct,0)) from employees e;
AVG(NVL(E.COMMISSION_PCT,0))
----------------------------
0.0716981132075472
建立資料組(GROUPBY)：
可以根據需要将查詢到的結果集資訊劃分為較小的組，用 GROUP BY 子句實作。
GROUP BY 子句用法：
GROUP BY 子句：GROUP BY 子句可以把表中的行劃分為組。然後可以用組函數傳回每一組的摘要資訊
使用分組原則：
• 如果在 SELECT 子句中包含了組函數，就不能選擇單獨的結果，除非單獨的列出現在 GROUP BY 子句中。如果未能在 GROUP BY 子句中包含一個字段清單，你會收到一個錯誤資訊。
• 使用 WHERE 子句，你可以在劃分行成組以前過濾行。
• 在 GROUP BY 子句中必須包含列。
• 在 GROUP BY 子句中你不能用列别名。
• 預設情況下，行以包含在 GROUP BY 清單中的字段的升序排序。可以用 ORDER BY子句覆寫這個預設值。
GROUP BY 子句 的使用：
我們可以根據自己的需要對資料進行分組，在分組時，隻要将需要做分組的列的列名添加到 GROUP BY 子句後側就可以。GROUP BY 列不必在 SELECT 清單中。
例：求每個部門的平均薪水
SQL> select avg(e.salary),e.department_id from employees e where e.department_id is not null group by e.department_id;
AVG(E.SALARY) DEPARTMENT_ID
------------- -------------
8601.33333333 100
4150 30
19333.3333333 90
9500 20
10000 70
10154 110
3475.55555555 50
8936.36363636 80
6500 40
5760 60
4400 10
11 rows selected
多于一個列的分組:
例：顯示在每個部門中付給每個工作崗位的合計薪水的報告。
SQL> select sum(e.salary),e.job_id,e.department_id from employees e
2 group by e.department_id,e.job_id;
SUM(E.SALARY) JOB_ID DEPARTMENT_ID
------------- ---------- -------------
8300 AC_ACCOUNT 110
34000 AD_VP 90
55700 ST_CLERK 50
233900 SA_REP 80
36400 ST_MAN 50
61000 SA_MAN 80
12008 AC_MGR 110
24000 AD_PRES 90
28800 IT_PROG 60
12008 FI_MGR 100
13900 PU_CLERK 30
64300 SH_CLERK 50
13000 MK_MAN 20
39600 FI_ACCOUNT 100
7000 SA_REP
10000 PR_REP 70
11000 PU_MAN 30
4400 AD_ASST 10
6000 MK_REP 20
6500 HR_REP 40
20 rows selected
GROUP BY 子句執行的順序
先進行資料查詢，在對資料進行分組，然後執行組函數。
限制分結果（having子句）
having子句通常都是和group by一起用，可以放在group by的前面，也可以放在後面（建議：放在group by的後面，友善自己檢視。），having子句是用來彌補group by子句不能用where條件語句的不足，它的出現就是為了和where子句一樣的效果，
HAVING 的用法：
例：顯示那些最高薪水大于 $10,000 的部門的部門号和最高薪水
SQL> select max(e.salary),e.department_id from employees e
2 group by e.department_id
3 having max(e.salary)>10000;
MAX(E.SALARY) DEPARTMENT_ID
------------- -------------
12008 100
11000 30
24000 90
13000 20
12008 110
14000 80
6 rows selected
例：查詢那些最高薪水大于 $10,000 的部門的部門号和平均薪水
SQL> select avg(e.salary),e.department_id from employees e
2 group by e.department_id
3 having max(e.salary)>10000;
AVG(E.SALARY) DEPARTMENT_ID
------------- -------------
8601.33333333 100
4150 30
19333.3333333 90
9500 20
10154 110
8936.36363636 80
6 rows selected
嵌套組函數：
在使用組函數時我們也可以根據需要來做組函數的嵌套使用。
例：顯示部門中的最大平均薪水：
SQL> select max(avg(e.salary))from employees e group by e.department_id;
MAX(AVG(E.SALARY))
------------------
19333.3333333333
組函數小節練習：
1.顯示所有雇員的最高、 、 最低、 、 合計和平均薪水， ， 列标簽分别為 ：Maximum 、Minimum 、Sum 和 和 Average 。四 舍五入結果為最近的整數 ：
SQL> select max(e.salary) Maximum,min(e.salary) Minimum,sum(e.salary) Sum,avg(e.salary) Average from employees e
2 ;
MAXIMUM MINIMUM SUM AVERAGE
---------- ---------- ---------- ----------
24000 2100 681816 6432.22641
2.修改上題 顯示每中工作類型的最低、最高、合計和平均薪水：
SQL> select e.job_id,max(e.salary) Maximum,min(e.salary) Minimum,sum(e.salary) Sum,avg(e.salary) Average from employees e group by e.job_id;
JOB_ID MAXIMUM MINIMUM SUM AVERAGE
---------- ---------- ---------- ---------- ----------
IT_PROG 9000 4200 28800 5760
AC_MGR 12008 12008 12008 12008
AC_ACCOUNT 8300 8300 8300 8300
ST_MAN 8200 5800 36400 7280
PU_MAN 11000 11000 11000 11000
AD_ASST 4400 4400 4400 4400
AD_VP 17000 17000 34000 17000
SH_CLERK 4200 2500 64300 3215
FI_ACCOUNT 9000 6900 39600 7920
FI_MGR 12008 12008 12008 12008
PU_CLERK 3100 2500 13900 2780
SA_MAN 14000 10500 61000 12200
MK_MAN 13000 13000 13000 13000
PR_REP 10000 10000 10000 10000
AD_PRES 24000 24000 24000 24000
SA_REP 11500 6100 240900 8306.89655
MK_REP 6000 6000 6000 6000
ST_CLERK 3600 2100 55700 2785
HR_REP 6500 6500 6500 6500
19 rows selected
3.寫一個查詢顯示每一工作崗位的人數：
SQL> select e.job_id,count(e.employee_id) from employees e group by e.job_id;
JOB_ID COUNT(E.EMPLOYEE_ID)
---------- --------------------
AC_ACCOUNT 1
AC_MGR 1
AD_ASST 1
AD_PRES 1
AD_VP 2
FI_ACCOUNT 5
FI_MGR 1
HR_REP 1
IT_PROG 5
MK_MAN 1
MK_REP 1
PR_REP 1
PU_CLERK 5
PU_MAN 1
SA_MAN 5
SA_REP 29
SH_CLERK 20
ST_CLERK 20
ST_MAN 5
19 rows selected
4.确定經理人數，不需要列出他們，列标簽是 Number ofManagers 。
SQL> select count(distinct e.manager_id) from employees e;
COUNT(DISTINCTE.MANAGER_ID)
---------------------------
18
5.寫一個查詢顯示最高和最低薪水之間的差。列标簽是DIFFERENCE 。
SQL> select (max(e.salary)-min(e.salary)) DIFFERENCE from employees e;
DIFFERENCE
----------
21900
6.顯示經理号和經理付給雇員的最低薪水。 。 排除那些經理未知的人 。排除最低薪水小于等于 $6,000 的組 。 按薪水降序排序輸出。
SQL> select e.manager_id, min(e.salary) from employees e where e.manager_id is not null
2 group by e.manager_id
3 having min(e.salary)<=6000
4 order by min(e.salary) desc;
MANAGER_ID MIN(E.SALARY)
---------- -------------
201 6000
100 5800
101 4400
103 4200
123 2500
114 2500
124 2500
122 2200
120 2200
121 2100
10 rows selected
7. 寫一個查詢顯示每個部門的名字、地點、人數和部門中所有雇員的平均薪水。四舍五入薪水到兩位小數
SQL> select d.department_name,d.location_id,count(e.employee_id),round(avg(e.salary),2) from employees e,departments d
2 where e.department_id=d.department_id
3 group by d.department_name,d.location_id;
DEPARTMENT_NAME LOCATION_ID COUNT(E.EMPLOYEE_ID) ROUND(AVG(E.SALARY),2)
------------------------------ ----------- -------------------- ----------------------
Administration 1700 1 4400
Marketing 1800 2 9500
Sales 2500 33 8936.36
Purchasing 1700 6 4150
Finance 1700 6 8601.33
IT 1400 5 5760
Executive 1700 3 19333.33
Shipping 1500 45 3475.56
Accounting 1700 2 10154
Human Resources 2400 1 6500
Public Relations 2700 1 10000
8.建立一個查詢顯示雇員總數，和在 2001 、2002 、2003 和受雇的雇員人數。建立适當的列标題。
SQL> select count(e.employee_id),sum(decode(to_char(e.hire_date,'yyyy'),'2001',1,0)) "2001",sum(decode(to_char(e.hire_date,'yyyy'),'2002',1,0)) "2002",sum(decode(to_char(e.hire_date,'yyyy'),'2003',1,0)) "2003" from employees e;
COUNT(E.EMPLOYEE_ID) 2001 2002 2003
-------------------- ---------- ---------- ----------
106 1 7 6
9.建立一個混合查詢顯示工作崗位和工作崗位的薪水合計，并門 且合計部門 20 、50 、80 和 和 90 的工作崗位的薪水。給每
列一個恰當的列标題
SQL> select e.job_id,sum(e.salary),sum(decode(e.department_id,20,e.salary)) "dept_20",sum(decode(e.department_id,50,e.salary)) "dept_50",sum(decode(e.department_id,20,e.salary)) "dept_80",sum(decode(e.department_id,90,e.salary)) "dept_90" from employees e group by e.job_id;
JOB_ID SUM(E.SALARY) dept_20 dept_50 dept_80 dept_90
---------- ------------- ---------- ---------- ---------- ----------
IT_PROG 28800
AC_MGR 12008
AC_ACCOUNT 8300
ST_MAN 36400 36400
PU_MAN 11000
AD_ASST 4400
AD_VP 34000 34000
SH_CLERK 64300 64300
FI_ACCOUNT 39600
FI_MGR 12008
PU_CLERK 13900
SA_MAN 61000
MK_MAN 13000 13000 13000
PR_REP 10000
AD_PRES 24000 24000
SA_REP 240900
MK_REP 6000 6000 6000
ST_CLERK 55700 55700
HR_REP 6500
19 rows selected