分组函数

组函数类型

avg

count

max

min

sum

 

 

select avg(salary),max(salary),min(salary),sum(salary) from employees;

AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY)
----------- ----------- ----------- -----------
6461.682243       24000        2100      691400 

 

 

--count（e） 返回e不为空的记录总数

select count(employee_id),count(last_name),count(hire_date) from employees;

COUNT(EMPLOYEE_ID) COUNT(LAST_NAME) COUNT(HIRE_DATE)
------------------ ---------------- ----------------
               107              107              107 

 

-- 非空的有35个

select count(commission_pct) from employees;

COUNT(COMMISSION_PCT)
---------------------
                   35 

 

 

-- 使用表达式仅仅计算是非空的

select avg(commission_pct), sum(commission_pct)/count(commission_pct),sum(commission_pct)/107 from employees;

AVG(COMMISSION_PCT) SUM(COMMISSION_PCT)/COUNT(COMMISSION_PCT) SUM(COMMISSION_PCT)/107
------------------- ----------------------------------------- -----------------------
       0.2228571429                              0.2228571429           0.07289719626 

 

--求出employees表中各部门的平均工资

select department_id,avg(salary)
from employees
group by department_id;

DEPARTMENT_ID AVG(SALARY)
------------- -----------
                     7000 
          100        8600 
           30        4150 
           90 19333.33333 
           20        9500 
           10        4400 

 选定了 12 行 

--  在20，30，40中的

select department_id,avg(salary)
from employees
where department_id  in(20,30,40)
group by department_id;

 

--多层分组的话

select department_id,job_id,avg(salary)
from employees
group by department_id,job_id;

 

注意：查询列表中只要不是组函数的列都该出现在group by 中

 

--Having 的使用 

--求出各部门中平均工资大于6000的部门，以及其平均工资   having替换where

select department_id,avg(salary)
from employees
having avg(salary)>6000
group by department_id order by department_id asc;

DEPARTMENT_ID AVG(SALARY)
------------- -----------
           20        9500 
           40        6500 
           70       10000 
           80 8955.882353 
           90 19333.33333 
          100        8600 
          110       10150 
                     7000 

 选定了 8 行 

 

--组函数是可以嵌套的

select avg(max(salary)) from employees 
group by department_id;

AVG(MAX(SALARY))
----------------
           10925 

 

 

练习：

--1.查询 employees 表中有多少个部门

select count (distinct department_id ) from employees;

COUNT(DISTINCTDEPARTMENT_ID)
----------------------------
                          11 

 

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

AVG(NVL(COMMISSION_PCT,0))
--------------------------
             0.07289719626 

 

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

select department_id,avg(salary) from employees
group by department_id;

DEPARTMENT_ID AVG(SALARY)
------------- -----------
                     7000  
           30        4150 
           90 19333.33333 
           10        4400 

 选定了 12 行 

 

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

select 'Toronto',avg(salary)
from employees e join departments d
on  e.department_id = d.department_id
join locations l
on d.location_id = l.location_id
where city = 'Toronto';

'TORONTO' AVG(SALARY)
--------- -----------
Toronto          9500 

 

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

select city,avg(salary)
from employees e join departments d
on  e.department_id = d.department_id
join locations l
on d.location_id = l.location_id
group by city;

CITY                           AVG(SALARY)
------------------------------ -----------
London                                6500 
Seattle                        8844.444444 
Munich                               10000
Oxford                         8955.882353 

 选定了 7 行 

 

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

SELECT department_id, avg(salary)
FROM employees e 
HAVING avg(salary) > 8000    
GROUP BY department_id

DEPARTMENT_ID AVG(SALARY)
------------- -----------
          100        8600 
           90 19333.33333 
           20        9500 
           70       10000 
          110       10150 
           80 8955.882353 

 选定了 6 行 

 

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

SELECT job_title,avg(salary)
FROM employees e join jobs j
on e.job_id = j.job_id
HAVING avg(salary) > 6000    
GROUP BY job_title;

JOB_TITLE                           AVG(SALARY)
----------------------------------- -----------
Sales Representative                       8350 
Accounting Manager                        12000 
Public Relations Representative           10000 
Administration Vice President             17000 

 

-- 8 .查询所有部门的名字，location_id，员工数量和工资平均值 （右外连接  有的部门没有员工  emp表的数据少

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

DEPARTMENT_NAME                LOCATION_ID COUNT(EMPLOYEE_ID) AVG(SALARY)
------------------------------ ----------- ------------------ -----------
Administration                        1700                  1        4400 
Marketing                             1800                  2        9500 
Shareholder Services                  1700                  0             
Operations                            1700                  0             

 

 

9.查询公司在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_char(hire_date,'yyyy') in ('1995','1996','1997','1998');

     total       1995       1996       1997       1998
---------- ---------- ---------- ---------- ----------
        65          4         10         28         23