组函数

AVG

COUNT

MAX

MIN

SUM

VARIANCE：方差

STDDEV：标准差

SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)

FROM employees WHERE job_id LIKE '%REP%';

SELECT MIN(hire_date), MAX(hire_date) FROM employees;

COUNT(expr) 返回expr为非空值的行数:

SELECT COUNT(commission_pct) FROM employees WHERE department_id = 80;

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

select sum(comm),avg(comm) from emp;去除空值

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

SELECT COUNT(DISTINCT department_id) FROM employees;

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ;

SELECT AVG(salary) FROM employees GROUP BY department_id ;

Group by多列

SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id > 40

GROUP BY department_id, job_id

ORDER BY department_id;

group by不能使用别名,order by可以使用别名。

非法的组函数使用

SELECT department_id, COUNT(last_name) FROM employees;

SELECT department_id, job_id, COUNT(last_name) FROM employees GROUP BY department_id;

SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id;

正确使用having子句

SELECT column, group_function

FROM table

[WHERE condition]

[GROUP BY group_by_expression]

[HAVING group_condition]

[ORDER BY column];

SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary)>10000 ;

SELECT department_id, MAX(salary) FROM employees HAVING MAX(salary)>10000 GROUP BY department_id;

SELECT job_id, SUM(salary) PAYROLL FROM employees WHERE job_id NOT LIKE '%REP%'

GROUP BY job_id HAVING SUM(salary) > 13000 ORDER BY SUM(salary);

组函数嵌套

SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id;

组函数只能嵌套一层，当行函数可以嵌套多层。必须跟group by。