Oracle数据库学习（5）分组函数

一：聚合函数

avg：平均函数

max：最大值函数

min：最小值函数

sum：求和函数

stddev：标准差函数

count()：计数函数

 

例子1：对员工表查询平均工资、最高工资、最低工资、标准差工资、总工资、总员工数

SELECT ROUND(AVG(SALARY),2) "平均工资",MAX(SALARY) "最高工资",MIN(SALARY) "最低工资",ROUND(stddev(SALARY), 2) "标准差工资",SUM(SALARY) "总工资" ,COUNT(EMPLOYEE_ID) "总员工数" FROM EMPLOYEES

 

例子2：通过DISTINCT统计员工的部门数量

SELECT COUNT(DISTINCT DEPARTMENT_ID) FROM EMPLOYEES;

 

 

 

二：group by：对字段分组

1、对单列分组：查询employees表中各部门的平均工资

SELECT DEPARTMENT_ID,AVG(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID;

2、对多列分组：查询employees表中各部门、各工种的平均工资

SELECT DEPARTMENT_ID,JOB_ID,AVG(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID,JOB_ID ORDER BY DEPARTMENT_ID;

 

三：having：分组后过滤条件

1、求出各部门中平均工资大于6000的部门

SELECT DEPARTMENT_ID,AVG(SALARY) FROM EMPLOYEES 
GROUP BY DEPARTMENT_ID 
HAVING AVG(SALARY)>6000
ORDER BY DEPARTMENT_ID;

 

四：嵌套聚合函数

1、求出各部门中平均工资的最大值

SELECT MAX(AVG(SALARY)) FROM EMPLOYEES GROUP BY DEPARTMENT_ID

 

 

五：练习例题：

1、查询公司员工工资的最大值，最小值，平均值，总和

select max(salary),min(salary),avg(salary),sum(salary) from employees

2、查询各job_id的员工工资的最大值，最小值，平均值，总和

select job_id,max(salary),min(salary),avg(salary),sum(salary) from employees group by job_id

3、选择各个job_id的员工人数

select job_id,count(employee_id) from employees group by job_id

4、查询员工最高工资和最低工资的差距（DIFFERENCE）

select max(salary),min(salary),max(salary)-min(salary) "DIFFERENCE" from employees

5、查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

select manager_id,min(salary) from employees where manager_id is not null group by manager_id having min(salary) >= 6000

6、查询所有部门的名字，location_id，员工数量和工资平均值

select department_name,location_id,count(employee_id),avg(salary) from employees e right outer join departments d on e.department_id = d.department_id
group by department_name,location_id

7、查询公司在1995-1998年之间，每年雇用的人数，结果类似下面的格式

total	1995	1996	1997	1998
20	3	4	6	7

select count(*) "total",
       count(decode(to_char(hire_date,'yyyy'),'1995',1,null)) "1995",
       count(decode(to_char(hire_date,'yyyy'),'1996',1,null)) "1996",
       count(decode(to_char(hire_date,'yyyy'),'1997',1,null)) "1997",
       count(decode(to_char(hire_date,'yyyy'),'1998',1,null)) "1998"
from employees
where to_char(hire_date,'yyyy') in ('1995','1996','1997','1998')