资源描述：
《用Group函数聚合数据》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、AggregatingDataUsingGroupFunctions云南财贸学院Copyright©OracleCorporation,2001.Allrightsreserved.ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:•Identifytheavailablegroupfunctions•Describetheuseofgroupfunctions•GroupdatausingtheGROUPBYclause•IncludeorexcludegroupedrowsbyusingtheHAVING
2、clause5-2Copyright©OracleCorporation,2001.Allrightsreserved.WhatAreGroupFunctions?Groupfunctionsoperateonsetsofrowstogiveoneresultpergroup.EMPLOYEESThemaximumsalaryintheEMPLOYEEStable.…5-3Copyright©OracleCorporation,2001.Allrightsreserved.TypesofGroupFunctions•AVG•COUNT•MAX•MIN•STDDEV•SUM•VARIANCE5-
3、4Copyright©OracleCorporation,2001.Allrightsreserved.GroupFunctionsSyntaxSELECT[column,]group_function(column),...FROMtable[WHEREcondition][GROUPBYcolumn][ORDERBYcolumn];5-5Copyright©OracleCorporation,2001.Allrightsreserved.UsingtheAVGandSUMFunctionsYoucanuseAVGandSUMfornumericdata.SELECTAVG(salary),
4、MAX(salary),MIN(salary),SUM(salary)FROMemployeesWHEREjob_idLIKE'%REP%';5-6Copyright©OracleCorporation,2001.Allrightsreserved.UsingtheMINandMAXFunctionsYoucanuseMINandMAXforanydatatype.SELECTMIN(hire_date),MAX(hire_date)FROMemployees;5-7Copyright©OracleCorporation,2001.Allrightsreserved.UsingtheCOUNT
5、FunctionCOUNT(*)returnsthenumberofrowsinatable.SELECTCOUNT(*)FROMemployeesWHEREdepartment_id=50;5-8Copyright©OracleCorporation,2001.Allrightsreserved.UsingtheCOUNTFunction•COUNT(expr)returnsthenumberofrowswithnon-nullvaluesfortheexpr.•DisplaythenumberofdepartmentvaluesintheEMPLOYEEStable,excludingth
6、enullvalues.SELECTCOUNT(commission_pct)FROMemployeesWHEREdepartment_id=80;5-9Copyright©OracleCorporation,2001.Allrightsreserved.UsingtheDISTINCTKeyword•COUNT(DISTINCTexpr)returnsthenumberofdistinctnon-nullvaluesoftheexpr.•DisplaythenumberofdistinctdepartmentvaluesintheEMPLOYEEStable.SELECTCOUNT(DIST
7、INCTdepartment_id)FROMemployees;5-10Copyright©OracleCorporation,2001.Allrightsreserved.GroupFunctionsandNullValuesGroupfunctionsignorenullvaluesinthecolumn.SELECTAVG(commission_pct)FROMemployees;5-11C
