组函数:
– 类型和语法
– 使用 AVG, SUM, MIN, MAX, COUNT
– 组函数使用 DISTINCT 关键字
– 组函数中NULL 值
分组函数:作用于一组数据,并对一组数据返回一个值
组函数类型
AVG 平均值
COUNT 统计值
MAX 最大值
MIN 最小值
SUM 合计
STDDEV 标准差
VARIANCE 方差
组函数语法:
select group_function(column), ... from table [where condition] [order by column];
使用 AVG 和 和 SUM 函数
可以对数值型数据使用 AVG 和 SUM 函数
1、查询job_id为REP的 平均工资,最高工资,工资总和
select avg(salary),max(salary),min(salary),sum(salary) from employees where job_id like '%REP%';
使用 MIN 和 和 MAX
可以对数值型、字符型和日期型使用 MIN 和 MAX 函数
2、查询入职时最短和最长时间
select min(hire_date),max(hire_date) from employees;
使用 COUNT
1、统计一下department_id 为50的部门有多少人
select count(*) from employees where department_id =50;
2、如果有空值不会被计算进去
select count(commission_pct) from employees where department_id=80;
3、显示 EMPLOYEES 表中不同的部门数
select count(distinct department_id) from employees;
组函数忽略空值
1、统计一下提成
select avg(commission_pct) from employees;
2、将所有的人都统计进来
select avg(nvl(commission_pct,0)) from employees;
分组数据:GROUP BY
可以使用GROUP BY 子句将表中的数据分成若干组.
group by 后面不能使用列别名,select 后面有限制.
1、求出EMPLOYEES中各个部门的平均工资
select department_id,avg(salary) from employees group by department_id order by department_id;
2、包含在 GROUP BY 子句中的列不必包含在SELECT 列表中。
select sum(salary) from employees group by job_id;
3、进行多组分列,按照部门和工作进行分组得到分组后工资的和
select department_id,job_id,sum(salary) from employees group by department_id,job_id order by department_id;
非法使用组函数
SELECT 列表中的列或表达式,未包含在组函数中的列,都必须包含于GROUP BY 子句中
错误:
select department_id, count(last_name) from employees;
或
select department_id, job_id, count(last_name) from employees group by department_id;
也就是说必须把department_id 和job_id 加入到group by 中
正确:
select department_id, count(last_name) from employees group by department_id;
或
select department_id, job_id, count(last_name) from employees group by department_id,job_id;
不能使用 WHERE 子句来过滤组
可以使用 HAVING 子句来过滤组
错误:
select department_id, avg(salary) from employees where avg(salary) > 8000 group by department_id;
过滤分组:HAVING 子句
使用 HAVING 子句过滤分组条件:
行已经被分组。
使用了组函数。
满足HAVING 子句中条件的分组将被显示
语法:
select column, group_function from table [where condition]
[group by group_by_expression]
[having group_condition]
[order by column];
1、每个部门的最高薪水大于$10,000
select department_id,max(salary) from employees group by department_id having max(salary)>10000;
2.查找不是REP工作的工资总和大于13000的,并按照sum salary排序。
select job_id ,sum(salary) from employees where job_id not like '%REP%' group by job_id having sum(salary) > 13000
order by sum(salary);
嵌套组函数
按照部门分类显示平均工资的最大值:
select max(avg(salary)) from employees group by department_id;
但是嵌套组函数好像不能在添加新的列了
练习题:
1、找出所有员工工资的最大值,最小值和以及平均值。并以此将各列的别名修改
为”Maximum”,”Minimum”,”Sum”,”Average”。并且要求将结果进行四舍五入。
select round(max(salary) ,0) "Maxinmum", round(min(salary),0) Minimum, round(sum(salary),0) Sum ,round(avg(salary),0) Average from employees;
2、以 job_id 进行分组,查看每个工种的工资的最大值,最小值,和,以及平均值
select job_id,max(salary),min(salary),sum(salary),avg(salary) from employees group by job_id;
3、写一个查询语句,统计每一个工种的员工数
select job_id,count(employee_id)from employees group by job_id;
4、让HR部门的同事可以输入一个工种,然后 SQL 返回该工种的员工数量。
select job_id,count(*) from employees where job_id like '&job_title' group by job_id;
5、直接显示出所有经理的总人数。并将该列标记为"Number of Managers".提示:使用 MANAGER_ID 这一列来确定经理的数量
select count(distinct manager_id) "Number of Managers" from employees;
6、查出薪水最高的和薪水最低的差值,并将该列标记为 DIFFERENCE
select max(salary) - min(salary) "DIFFERENCE" from employees;
7、请查询出每个经理手下工资最低的员工,那些没有经理的员工需要排除,并且需要排除那些最低薪水
小于等于6000 组。最后将结构根据薪水以降序排列。
select manager_id,min(salary)
from employees
where manager_id is not null
group by manager_id
having min(salary) > 6000
order by min(salary) desc;
8、请编写一条 SQL 语句,查看员工的总数,以及在 1996,1997,1998,1999 这几年被雇佣的员工数量,并为各列取合适的别名。
select count(*) total,
sum(decode(to_char(hire_date, 'fm YYYY'),1999,1,0)) "1999",
sum(decode(to_char(hire_date, 'fm YYYY'),1998,1,0)) "1998",
sum(decode(to_char(hire_date, 'fm YYYY'),1997,1,0)) "1997",
sum(decode(to_char(hire_date, 'fm YYYY'),1996,1,0)) "1996"
from employees;
9、请通过一个矩阵显示出所需要的结果,要求是根据部门编号(20,50,80,90)算出对应的工种的工
资,以及该工种的工资总和,对于部门号是 20,50,80,90 这几列来说,请给出一个合适的别名。
select job_id "job",
sum(decode(department_id,20,salary)) "dept 20",
sum(decode(department_id,50,salary)) "dept 50",
sum(decode(department_id,80,salary)) "dept 80",
sum(decode(department_id,90,salary)) "dept 90",
sum(salary) "total"
from employees
group by job_id;