分组函数计算

count

--求某列的最大值

select max(salary) from emp_ning;

select max(s1) from ielts_ning;

select max(calculate_ning((s1+s2+s3+s4)/4))

from ielts_ning;

select min(salary) from emp_ning

select min(s1) from ielts_ning;

--组函数忽略空值

select avg(nvl(salary, 0))from emp_ning;

select sum(salary) / count(salary)from emp_ning;

count / sum / avg / max / min

 select deptno, count(*) from emp_ning where deptno is not null group by deptno;

--没有group by短语,语法错误

 select deptno, count(*) from emp_ning where deptno is not null;

--没有语法错误,信息不全

 select count(*) from emp_ning where deptno is not null group by deptno;

--查询各个部门中的最多人数

 select max(count(*)) from emp_ning where deptno is not null group by deptno;

--哪个部门的人数最多?

select deptno, count(*) from emp_ning where deptno is not null group by deptno having count(*) = 4;

--查找人数最多的部门号

--10  6

select deptno, count(*) from emp_ning where deptno is not null group by deptno

having count(*) = ( select max(count(*)) from emp_ning where deptno is not null group by deptno);

--人数最多的部门的名字和工作地点

select dname, location from dept_ning where deptno = 10;

select dname, location from dept_ning

where deptno = ( select deptno, count(*) from emp_ning where deptno is not null group by deptno

         having count(*) = ( select max(count(*)) from emp_ning where deptno is not null group by deptno));

select 列, 组函数

from 表

where 条件

group by ...

having...

order by...

--表里没有的数据,需要计算的数据做条件,用having

--表里有的数据, 做条件,用where

--所有的组函数做条件,必须用having

10   10000

20   5000

30   8000

7000

--哪些部门的平均工资比整个机构的平均工资高?

--查询的条件是平均工资,是组函数计算出来的结--果,所以使用having, 而不是where

select deptno, avg(nvl(salary,0)) from emp_ning group by deptno having avg(nvl(salary,0)) > (

select avg(nvl(salary,0)) from emp_ning);

--哪个部门的人数超过5个人?

select deptno, count(*) from emp_ning group by deptno having count(*) > 5;

--哪个部门的薪水总和比部门20的薪水总和高?

select deptno, sum(salary) from emp_ning group by deptno

having sum(salary) > ( select sum(salary) from emp_ning where deptno = 20);

name job     salary

a   android  8000

b   java     5000

c   c++      6000

d   java     10000

e   android  9000

....

select job, avg(salary) from mytable group by job;