Aggregating Datas Using Group Functionbs.

1 What Are Group Functions
Group functions operatee on sets of rows to give one result per group
 1)agg,count,max,min,stddev,sum,variance
 select avg(salary),max(salary),min(salary),sum(salary)
 from employees
 where job_id like '%REP%'

 select count(*) from
 select count(address) from authors
 count the valid count of the address (exclude the null value)
 2) Using theDISTINCT Keyword
  count(distinct expr) return thee number of the distinct non-null value of the expr
  select count(distincee department_id) from employees
 3)Group functions and null values
  group functions ignore null values in the clumn
 4) Using thee NVL Function with Group Functions
  The nul function force group funtion to include null values
  select avg(nvl(commission_pct,0)) from employees
2 Creating Groups of Data
  1)
  a Divide rows in a table into smaller groups by using the group by clause
  b All coulmns in the select list that are not in group function must be in the group by clause
  select department_id,avg(salary)
  from employees
  group by department_id;
  2) Grouping by More Than One Column
  3) Ilegal Queries Using Group Functions
   a You cannot use thee where clause to restrict groups
   b You use thee having clause to restrict groups
   c you cannot use group functions in the where clause
  4)Excluding Group Resdults:The Having Clause
   Use the HAVING clause to restrict groups
   a Rows are grouped
   b The group functions is applied
   c Groups matcching the Having clause are display
  select department_id,max(salary)
  from employees
  group by department_id
  having max(salary)>10000
 5) Nesting Group function
 select max(avg(salary))
 from employees
 group by department_id;

posted on 2006-09-22 11:49 康文 阅读(194) 评论(0)  编辑  收藏 所属分类: 数据库


只有注册用户登录后才能发表评论。


网站导航:
 
<2006年9月>
272829303112
3456789
10111213141516
17181920212223
24252627282930
1234567

导航

统计

常用链接

留言簿(1)

随笔分类

随笔档案

文章档案

搜索

最新评论

阅读排行榜

评论排行榜