Oracle（六）分组函数

格式：

SELECT	column, group_function
FROM		table
[WHERE	condition]
[GROUP BY	group_by_expression]
[HAVING	group_condition]
[ORDER BY	column];

组函数

avg sum count max min

avg 和 sum 只适用于数值型的

select avg(salary),sum(salary)
from employees;

select avg(hire_date),avg(last_name)
from employees;

max,min可以处理数值型、字符型、Date类型

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

count计算时，空值不计算在内。

select count(employee_id),count(1),count(2),count(salary),count(*),count(hire_date),count(commission_pct)
from employees;

select avg(salary),sum(salary)/count(salary)
from employees;
--avg = sum / count

select avg(commission_pct),sum(commission_pct)/count(commission_pct),sum(commission_pct)/107
from employees;

NVL函数使分组函数无法忽略空值

select avg(nvl(commission_pct,0)),sum(commission_pct)/107
from employees;

COUNT(DISTINCT expr)返回expr非空且不重复的记录总数

SELECT COUNT(distinct department_id)
FROM   employees;

使用GROUP BY 子句对数据分组

--group by
--求出EMPLOYEES表中各部门的平均工资
select department_id,avg(salary),max(salary),sum(salary)
from employees
group by department_id;

select job_id,avg(salary)
from employees
group by job_id;

--如果select中出现了组函数，同时出现没有使用组函数的列，如果不使用group by 就会出错。
--凡是不是组函数的列，都应该出现在group by 中
select department_id,job_id,avg(salary),sum(salary)
from employees
group by department_id,job_id
order by department_id;

使用HAVING 子句过滤分组结果集

• 行已经被分组。
• 使用了组函数。
• 满足HAVING 子句中条件的分组将被显示

--having的使用,用来过滤数据。尤其是当过滤条件出现组函数。
--查询各个部门的平均工资高于6000的部门有哪些，以及其平均工资
select department_id,avg(salary)
from employees
--where avg(salary) > 6000
group by department_id
having avg(salary) > 6000;