Chapter 6 : 综合数据和分组函数

Chapter 6 : 综合数据和分组函数

COUNT
AVE
SUM
MAX
MIN

分组函数必须要在group by 的情况下才可以使用奥

select count(*)
from emp e;

select count(e.mgr)
from emp e;
//return 不为空(NULL)的员工

select avg(e.sal) "Average Salary", sum(e.sal) "Summary", count(e.sal) "Records"
from emp e;

select min(e.sal) "Lowest Salary", max(e.sal) "Highest Salary"
from emp e;

select min(e.job), max(e.job)
from emp e;

select min(e.hiredate) "First Day", max(e.hiredate) "Last Day"
from emp e;

select e.job, avg(e.sal) "Average Salary"
from emp e
group by e.job;

select e.job, avg(e.sal) "Average Salary"
from emp e
group by e.job
order by "Average Salary" desc;

select avg(e.sal) "Average Salary"
from emp e
group by e.job;

/*******************************
1.分组
2.having对分组进行条件判断
NOTE:having是对于组而言的，where 是对于记录而言的

1.where 单个记录
2.group by
3.avg的执行
4.having 组记录
5.order by ...
*******************************/

select e.job, avg(e.sal)
from emp e
having avg(e.sal) > 1500
group by e.job



select e.job, avg(e.sal)
from emp e
group by e.job
having avg(e.sal) > 1500
order by 2;


//不知道下面这句的执行时机是什么样子的。
select e.job, avg(e.sal)
from emp e
group by e.job
having avg(e.sal) > 1500
order by avg(e.sal);


/****************************************
1.select * from ** where **
2.group by
3.avg  //组函数
4.min

****************************************/


select min(avg(e.sal)),max(avg(e.sal))
from emp e
where e.job not like 'PRESI%'
group by e.job;


//avg 不包括NULL的记录
select avg(e.comm) "Average Commission"
from emp e

select avg(e.comm) "Average Commission", sum(e.comm) "Summary Commission",
       e.job, count(e.comm) "Records"
from emp e
group by e.job;

select avg(nvl(e.comm, 0)) "Average Commission", sum(nvl(e.comm,0)) "Summary Commission",
       e.job, count(e.comm) "Records"
from emp e
group by e.job;