oracle分组函数

oracle基础
oracle单行函数
oracle多表查询
oracle分组函数
oracle子查询

/*
	AVG(expr)         平均值   必须是number类型
	COUNT(expr)       统计条数 可以是任意数据类型
	MAX(expr)         最大值   可以是任意数据类型
	MIN(expr)         最小值   可以是任意数据类型 
	SUM(expr)         求和     必须是number类型
*/


SELECT ROUND(AVG(SALARY), 2),MAX(SALARY),MIN(SALARY),SUM(SALARY)
FROM EMPLOYEES
-- 姓名字母排序最大，与最小，日期最早与最晚
SELECT MAX(LAST_NAME),MIN(LAST_NAME),MAX(HIRE_DATE),MIN(HIRE_DATE)
FROM EMPLOYEES
--count(列名) 
-- EMPLOYEE_ID 不为空有多少行，HIRE_DATE不为空有多少行，LAST_NAME不为空有多少行  
-- count(1),count(2),count(*) 表示的是统计这个表有多少行，跟里面的数据没有关系
--107 条
SELECT COUNT(EMPLOYEE_ID),COUNT(HIRE_DATE),COUNT(LAST_NAME)
FROM EMPLOYEES

-- COMMISSION_PCT 不为空有多少行
-- 35条
SELECT COUNT(COMMISSION_PCT)
FROM EMPLOYEES


-- 不忽略 空值做法
-- AVG(COMMISSION_PCT)忽略了空值 所以结果不对
SELECT AVG(COMMISSION_PCT),SUM(COMMISSION_PCT)/COUNT(NVL(COMMISSION_PCT, 1)),SUM(COMMISSION_PCT)/COUNT(107)
FROM EMPLOYEES

-- DISTINCT 去重复
-- 返回公司所有部门的个数

SELECT COUNT(DISTINCT DEPARTMENT_ID)
FROM EMPLOYEES

--GROUP BY 
-- 列中非分组函数包裹的必须加入到 GROUP BY中
-- 不能在 WHERE子句中使用分组函数


-- 求公司各个部门的平均工资
SELECT DEPARTMENT_ID,ROUND(AVG(SALARY),2)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID

-- 求公司40,60,80部门的平均工资
SELECT DEPARTMENT_ID,ROUND(AVG(SALARY),2)
FROM EMPLOYEES
WHERE DEPARTMENT_ID in(40,60,80)
GROUP BY DEPARTMENT_ID

-- 不同部门的不同工种的平均工资

SELECT DEPARTMENT_ID,JOB_ID,ROUND(AVG(SALARY),2)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID,JOB_ID


--求出各部门中平均工资大于6000的部门，以及其平均工资
SELECT DEPARTMENT_ID,AVG(SALARY)
FROM EMPLOYEES
HAVING AVG(SALARY)>6000
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID asc

-- 显示各个部门平均工资的最大值

SELECT MAX(AVG(SALARY))
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID


----------------------------------------------------------------------

-- 练习
-- 33. 查询 employees 表中有多少个部门
SELECT COUNT(DISTINCT DEPARTMENT_ID)
FROM EMPLOYEES

--34. 查询全公司奖金基数的平均值(没有奖金的人按 0 计算)

SELECT AVG(NVL(COMMISSION_PCT, 0))
FROM EMPLOYEES


-- 35. 查询各个部门的平均工资

SELECT AVG(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID


-- 36. Toronto 这个城市的员工的平均工资

SELECT  l.CITY,AVG(e.SALARY)
FROM EMPLOYEES e
LEFT JOIN DEPARTMENTS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
LEFT JOIN LOCATIONS l
ON d.LOCATION_ID = l.LOCATION_ID
WHERE l.CITY = 'Toronto'
GROUP BY l.CITY


-- 37. (有员工的城市)各个城市的平均工资


SELECT  l.CITY,AVG(e.SALARY)
FROM EMPLOYEES e
LEFT JOIN DEPARTMENTS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
LEFT JOIN LOCATIONS l
ON d.LOCATION_ID = l.LOCATION_ID
GROUP BY l.CITY

-- 38. 查询平均工资高于 8000 的部门 id 和它的平均工资.	


SELECT DEPARTMENT_ID,AVG(SALARY)
FROM EMPLOYEES
HAVING AVG(SALARY) >8000
GROUP BY DEPARTMENT_ID

		
-- 39. 查询平均工资高于 6000 的 job_title 有哪些


SELECT JOB_TITLE,AVG(SALARY)
FROM EMPLOYEES e 
LEFT JOIN JOBS j
ON e.JOB_ID = j.JOB_ID
HAVING AVG(SALARY) >6000
GROUP BY JOB_TITLE


-- 4.	查询公司员工工资的最大值，最小值，平均值，总和

SELECT MAX(SALARY),MIN(SALARY),AVG(SALARY),SUM(SALARY)
FROM EMPLOYEES


-- 5.	查询各job_id的员工工资的最大值，最小值，平均值，总和

SELECT JOB_ID,MAX(SALARY),MIN(SALARY),AVG(SALARY),SUM(SALARY)
FROM EMPLOYEES
GROUP BY JOB_ID


-- 6.	选择具有各个job_id的员工人数

SELECT JOB_ID,COUNT(JOB_ID)
FROM EMPLOYEES
GROUP BY JOB_ID

-- 7.	查询员工最高工资和最低工资的差距（DIFFERENCE）

SELECT MAX(SALARY),MIN(SALARY),(MAX(SALARY)-MIN(SALARY)) "DIFFERENCE"
FROM EMPLOYEES

-- 8.	查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

SELECT MANAGER_ID,MIN(SALARY)
FROM EMPLOYEES
WHERE MANAGER_ID is not null
GROUP BY MANAGER_ID
HAVING MIN(SALARY) >= 6000


-- 9.	查询所有部门的名字，location_id，员工数量和工资平均值

SELECT d.DEPARTMENT_NAME,d.LOCATION_ID,COUNT(EMPLOYEE_ID),AVG(SALARY)
FROM EMPLOYEES e,DEPARTMENTS d
WHERE e.DEPARTMENT_ID(+) = d.DEPARTMENT_ID
GROUP BY d.DEPARTMENT_NAME,d.LOCATION_ID


select department_name,location_id,count(employee_id),avg(salary)
from employees e right outer join departments d
on e.department_id = d.department_id
group by department_name,location_id




/*
10.	查询公司在1995-1998年之间，每年雇用的人数，结果类似下面的格式
total	1995	1996	1997	1998
20	   3	   4	   6	   7

*/


SELECT COUNT(*) "total" , 
			 COUNT(DECODE(TO_CHAR(HIRE_DATE,'yyyy'), '1995',1, NULL)) "1995",
			 
			 COUNT(DECODE(TO_CHAR(HIRE_DATE,'yyyy'), '1996',1, NULL)) "1996",
			 
			 COUNT(DECODE(TO_CHAR(HIRE_DATE,'yyyy'), '1997',1, NULL)) "1997",
			 
			 COUNT(DECODE(TO_CHAR(HIRE_DATE,'yyyy'), '1998',1, NULL)) "1998"
FROM EMPLOYEES
WHERE  TO_NUMBER(TO_CHAR(HIRE_DATE,'yyyy'))   BETWEEN 1995 AND 1998


SELECT COUNT(*) "total" , 
			 COUNT(CASE TO_CHAR(HIRE_DATE,'yyyy') WHEN '1995' THEN 1 ELSE NULL END) "1995",
			 
			 COUNT(CASE TO_CHAR(HIRE_DATE,'yyyy') WHEN '1996' THEN 1 ELSE NULL END) "1996",
			 
			 COUNT(CASE TO_CHAR(HIRE_DATE,'yyyy') WHEN '1997' THEN 1 ELSE NULL END) "1997",
			 
			 COUNT(CASE TO_CHAR(HIRE_DATE,'yyyy') WHEN '1998' THEN 1 ELSE NULL END) "1998"
FROM EMPLOYEES
WHERE  TO_NUMBER(TO_CHAR(HIRE_DATE,'yyyy'))  BETWEEN 1995 AND 1998