Oracle SQL Lesson (5)

组函数
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。