Oracle-OCP-SQL-17-Enhancements-to-the-GROUP-BY-Cla

Copyright©OracleCorporation,2001.Allrightsreserved.EnhancementstotheGROUPBYClause17-2Copyright©OracleCorporation,2001.Allrightsreserved.ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:•UsetheROLLUPoperationtoproducesubtotalvalues•UsetheCUBEoperationtoproducecross-tabulationvalues•UsetheGROUPINGfunctiontoidentifytherowvaluescreatedbyROLLUPorCUBE•UseGROUPINGSETStoproduceasingleresultset17-3Copyright©OracleCorporation,2001.Allrightsreserved.ReviewofGroupFunctionsGroupfunctionsoperateonsetsofrowstogiveoneresultpergroup.SELECT[column,]group_function(column)...FROMtable[WHEREcondition][GROUPBYgroup_by_expression][ORDERBYcolumn];SELECTAVG(salary),STDDEV(salary),COUNT(commission_pct),MAX(hire_date)FROMemployeesWHEREjob_idLIKE'SA%';Example:17-4Copyright©OracleCorporation,2001.Allrightsreserved.ReviewoftheGROUPBYClauseSELECTdepartment_id,job_id,SUM(salary),COUNT(employee_id)FROMemployeesGROUPBYdepartment_id,job_id;SELECT[column,]FROMtable[WHEREcondition][GROUPBYgroup_by_expression][ORDERBYcolumn];Example:Syntax:group_function(column)...17-5Copyright©OracleCorporation,2001.Allrightsreserved.ReviewoftheHAVINGClause•UsetheHAVINGclausetospecifywhichgroupsaretobedisplayed.•Youfurtherrestrictthegroupsonthebasisofalimitingcondition.SELECT[column,]group_function(column)...FROMtable[WHEREcondition][GROUPBYgroup_by_expression][HAVINGhaving_expression][ORDERBYcolumn];17-6Copyright©OracleCorporation,2001.Allrightsreserved.GROUPBYwithROLLUPandCUBEOperators•UseROLLUPorCUBEwithGROUPBYtoproducesuperaggregaterowsbycross-referencingcolumns.•ROLLUPgroupingproducesaresultssetcontainingtheregulargroupedrowsandthesubtotalvalues.•CUBEgroupingproducesaresultssetcontainingtherowsfromROLLUPandcross-tabulationrows.17-7Copyright©OracleCorporation,2001.Allrightsreserved.ROLLUPOperator•ROLLUPisanextensiontotheGROUPBYclause.•UsetheROLLUPoperationtoproducecumulativeaggregates,suchassubtotals.SELECT[column,]group_function(column)...FROMtable[WHEREcondition][GROUPBY[ROLLUP]group_by_expression][HAVINGhaving_expression];[ORDERBYcolumn];17-8Copyright©OracleCorporation,2001.Allrightsreserved.ROLLUPOperatorExampleSELECTdepartment_id,job_id,SUM(salary)FROMemployeesWHEREdepartment_id60GROUPBYROLLUP(department_id,job_id);12317-9Copyright©OracleCorporation,2001.Allrightsreserved.CUBEOperator•CUBEisanextensiontotheGROUPBYclause.•YoucanusetheCUBEoperatortoproducecross-tabulationvalueswithasingleSELECTstatement.SELECT[column,]group_function(column)...FROMtable[WHEREcondition][GROUPBY[CUBE]group_by_expression][HAVINGhaving_expression][ORDERBYcolumn];17-10Copyright©OracleCorporation,2001.Allrightsreserved.CUBEOperator:ExampleSELECTdepartment_id,job_id,SUM(salary)FROMemployeesWHEREdepartment_id60GROUPBYCUBE(department_id,job_id);123417-11Copyright©OracleCorporation,2001.Allrightsreserved.GROUPINGFunction•TheGROUPINGfunctioncanbeusedwitheithertheCUBEorROLLUPoperator.•UsingtheGROUPINGfunction,youcanfindthegroupsformingthesubtotalinarow.•UsingtheGROUPINGfunction,youcandifferentiatestoredNULLvaluesfromNULLvaluescreatedbyROLLUPorCUBE.•TheGROUPINGfunctionreturns0or1.SELECT[column,]group_function(column).,GROUPING(expr)FROMtable[WHEREcondition][GROUPBY[ROLLUP][CUBE]group_by_expression][HAVINGhaving_expression][ORDERBYcolumn];17-12Copyright©OracleCorporation,2001.Allrightsreserved.GROUPINGFunction:ExampleSELECTdepartment_idDEPTID,job_idJOB,SUM(salary),GROUPING(department_id)GRP_DEPT,GROUPING(job_id)GRP_JOBFROMemployeesWHEREdepartment_id50GROUPBYROLLUP(department_id,job_id);21317-13Copyright©OracleCorporation,2001.Allrightsreserved.GROUPINGSETS•GROUPINGSETSareafurtherextensionoftheGROUPBYclause.•YoucanuseGROUPINGSETStodefinemultiplegroupingsinthesamequery.•TheOracleServercomputesallgroupingsspecifiedintheGROUPINGSETSclauseandcombinestheresultsofindividualgroupingswithaUNIONALLoperation.•Groupingsetefficiency:–Onlyonepassoverthebasetableisrequired.–ThereisnoneedtowritecomplexUNIONstatements.–ThemoreelementstheGROUPINGSETShave,thegreatertheperformancebenefit.17-15Copyright©OracleCorporation,2001.Allrightsreserved.GROUPINGSETS:ExampleSELECTdepartment_id,job_id,manager_id,avg(salary)FROMemployeesGROUPBYGROUPINGSETS((department_id,job_id),(job_id,manager_id));12…17-17Copyright©OracleCorporation,2001.Allrightsreserved.CompositeColumns•Acompositecolumnisacollectionofcolumnsthataretreatedasaunit.ROLLUP(a,,d)•Tospecifycompositecolumns,usetheGROUPBYclausetogroupcolumnswithinparenthesessothattheOracleservertreatsthemasaunitwhilecomputingROLLUPorCUBEoperations.•WhenusedwithROLLUPorCUBE,compositecolumnswouldmeanskippingaggregationacrosscertainlevels.(b,c)17-19Copyright©OracleCorporation,2001.Allrightsreserved.CompositeColumns:ExampleSELECTdepartment_id,job_id,manager_id,SUM(salary)FROMemployeesGROUPBYROLLUP(department_id,(job_id,manager_id));213…17-21Copyright©OracleCorporation,2001.Allrightsreserved.ConcatenatedGroupings•Concatenatedgroupingsofferaconcisewaytogenerateusefulcombinationsofgroupings.•Tospecifyconcatenatedgroupingsets,youseparatemultiplegroupingsets,