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