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;