综合数据和分组函数

desc emp;

  

COUNT函数

select count(*) from emp;

 

select count(mgr) from emp;

 
AVG和SUM函数

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

 

MIN和MAX函数

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

 

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

 

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

 
GROUP BY子句

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

 

HAVING子句

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

 

分组函数的嵌套

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

 

 

分组函数的空值问题

除了count(*)以外，其他的分组函数都不处理空值(NULL)

select avg(comm) from emp;

 

select avg(comm), sum(comm), count(comm), job
from emp
group by job;

 
NVL函数在分组函数中的使用

select avg(nvl(comm, 0)) from emp;

 

select avg(nvl(comm, 0)), sum(nvl(comm, 0)), count(nvl(comm, 0)), job
from emp
group by job;