oracle 007-6：Aggregating Data Using Group Functions[使用group函数聚合数据]

#组函数
#除了count函数，其他组函数AVG/SUM/MAX/MIN/STDDEV/VARIANCE忽略空值进行计算
#组函数结果默认升序排列
----------------------------------------------------
/*107,包含空值记录*/
select count(*) from employees t
/*35,不包含空值记录*/
select count(t.commission_pct) from employees t
/*35,包含空值记录*/
select distinct t.commission_pct from employees t
/*0.222857142857143,不包含空值的行记录*/
select avg(t.commission_pct) from employees t
/*0.0728971962616822,强制包含空值的行记录*/
select avg(nvl(t.commission_pct,0)) from employees t
/*结果包含空值组*/   
select commission_pct from employees group by commission_pct
/*order by升序排列时，空格在前面，空值在最后*/          
select commission_pct from employees order by commission_pct
----------------------------------------------------
#GroupBy子句
select子句中的非组函数中出现了groupby子句中没有的字段，查询将报错
列的别名不能在groupby和where子句中使用
查询结果按groupby中的字段升序排列
当在groupby查询中需要使用orderby排序时，只能使用groupby中的字段进行排序
不能在where子句中限制需要的数据，应该使用having子句
组函数只能嵌套两层
orderby子句只能在最后
---------------------------------------------------
练习：
--select t.manager_id,min(salary) from employees t where t.manager_id is not null group by t.manager_id having min(salary)>6000 order by min(salary) desc
--select s.department_name,s.location_id,count(t.employee_id),to_char(avg(t.salary),'FM99999.99') from employees t,departments s where t.department_id=s.department_id group by s.department_name,s.location_id
--select job_id,decode(t.department_id,20,sum(salary)),decode(t.department_id,50,sum(salary)),decode(t.department_id,80,sum(salary)),decode(t.department_id,90,sum(salary)),sum(salary) from employees t group by t.job_id,t.department_id