分组函数使用

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

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

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

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

Sum和avg用在字符和日期

count返回表中的行数(包含重复行和包含空值)
select COUNT() from employees;

等同于OUNT()
select COUNT(1) from employees;

不计算空值
select COUNT(commission_pct) from employees;

select count(department_id) from employees;

不计算里面的空值

Distinct：不显示重复的值
select count(distinct department_id) from employees;

使用nvl函数处理空值，进行行数统计
select count(nvl(commission_pct,0) ) from employees;

使用where语句，进行统计行数
select COUNT(employee_id) from employees where department_id=90;

select first_name from employees
where department_id=30 order by first_name;

不能这样查询
select department_id, sum(salary) from employees;

选定的列要按分组方式显示group by
select department_id, sum(salary) from employees
group by department_id;

所选内容中的所有列都应按分组方式显示
select department_id, job_id,sum(salary) from employees
group by department_id, job_id;

select department_id, job_id,sum(salary) from employees
group by department_id,job_id
order by 1,2;

错误的写法
select department_id, job_id,sum(salary) from employees
group by department_id;

不能使用别名进行分组
select department_id d ,sum(salary) from employees
group by d;

select department_id d ,sum(salary) from employees
group by department_id
order by d;

先分组再排序
select department_id ,sum(salary) from employees
where department_id >30
group by department_id
order by department_id;

不能在where子句后面加分组函数
select department_id ,sum(salary) from employees
where sum(salary)>156400
group by department_id
order by department_id;

having子句：和group by一起使用，紧跟在group by的后边
select department_id ,sum(salary) from employees
group by department_id
HAVING sum(salary)>150000
order by department_id;

它可能是在分组之前 但不是建议的
select department_id ,sum(salary) from employees
HAVING sum(salary)>150000
group by department_id
order by department_id;

只能嵌套两个组函数 嵌套的组函数
select max(sum(salary)) from employees
group by department_id;

select sum(salary) from employees
group by department_id
order by 1;

select sum(salary) from employees
group by department_id
order by department_id;