分组函数

SQL> select count(*), sum(salary), avg(salary), min(salary), max(salary) from employees;

 

SQL> create table t1(x int);

SQL> insert into t1 values (null);

SQL> insert into t1 values (1);

SQL> commit;

SQL> select count(*) from t1;

SQL> select count(x) from t1;

SQL> select max(x) from t1;

SQL> select min(x) from t1;

SQL> select sum(x) from t1;

SQL> select avg(x) from t1;

 

SQL> select avg(salary), avg(nvl(commission_pct, 0)) from employees;

SQL> select count(distinct department_id) from employees; 去除重复值

 

Group by分组：

SQL> select department_id, avg(salary) from employees group by department_id;

多列分组：

SQL> select department_id, job_id, max(salary) from employees group by department_id, job_id;

SQL> select department_id, job_id, max(salary), last_name from employees group by department_id, job_id; 错误语法

 

练习：

公司中不同职位的数量

计算每个部门的人数

按年份分组，求员工的工资总和

 

Having语句：

SQL> select department_id, avg(salary) from employees where avg(salary)>=5000 group by department_id; 错误语句

SQL> select department_id, avg(salary) from employees group by department_id having avg(salary)>=5000;

 

练习：

按部门求出所有有部门的普通员工的平均工资，部门平均工资少于5000的不显示，最终结果按平均工资的降序排列。

select department_id, avg(salary) avg_sal

from employees

where job_id not like '%\_MGR' escape '\' and department_id is not null

group by department_id

having avg(salary)>=5000

order by avg_sal desc;