12-13 分组函数及练习

12、分组函数

多行函数

对一组数据处理，返回一个值。

使用groupby进行分组，使用过滤条件进行过滤。

组函数类型

avg

count

select max(last_name),min(last_name)
from employees;

案例

1

各个部门的平均工资
进行更进一步的细分

select department_id,avg(salary)
from employees
where department_id in (40,50)
group by department_id

select avg(salary)
from employees
group by department_id

select有的，groupby必须有，或者是在组函数中。

where子句中不能使用组函数，可以在having子句中使用组函数。

having中位置比较随意

求出各部门中平均工资大于6000的部门，以及平均工资
select department_id,avg(salary)
from employees
having avg(salary)>6000
group by department_id
order by department_id asc

组函数可以嵌套

输出所有部门的最高平均工资
select max(avg(salary))
from employees
group by department_id

13、练习

1

查有几个部门
select count(distinct department_id)
from employees

2

奖金平均值 这里注意 没有奖金要当成0计算 所以要用到nvl
select avg(nvl(commission_pct,0))
from employees

3 城市员工平均工资

select 'Toranto',avg(salary)
from employees e,departments d,locations l
where e.department_id=d.department_id and d.location_id=l.location_id
and l.city='Toranto'

4、（有员工的）各个城市的平均工资

select city,avg(salary)
from employees e,departments d,locations l
where e.department_id=d.department_id and d.location_id=l.location_id
group by city

5、查平均工资高于8000的部门以及它的id

select avg(salary) ,department_id
from employees e
group by department
having avg(salary)>8000

6、平均工资高于6000的job_title

select avg(salary),job_title
from employees e,jobs j
where e.job_id=j.job_id
group by job_title
having avg(salary)>6000

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

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

8、选择具有各个job_id的员工个数

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

如果数据都在一张表，做什么链表  傻逼

9、查询员工最高工资和最低工资的差距

select max(salary)-min(salary) “DIFFERENCE”

from employees e

10、查询所有部门的名字，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

11、在1995-1998年间，每年雇佣的人数

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