AllInformixdatabaseservers supportthefollowingaggregatefunctions:
v AVG v COUNT v MAX v MIN v RANGE v STDEV v SUM v VARIANCE
Anaggregatefunctionreturnsonevaluefora setofqueried rows.The aggregatefunctionstakeonvaluesthatdependonthesetofrowsthatthe
WHEREclauseoftheSELECTstatementreturns.IntheabsenceofaWHERE clause,theaggregatefunctionstakeonvaluesthatdependonall therows thattheFROM clauseforms.
Youcannotuseaggregatefunctionsforexpressionsthatcontainthefollowing datatypes: v TEXT v BYTE DynamicServer v CLOB v BLOB v
Opaquedatatypes(unlessuser createsaggregatefunctions)
v Collectiondatatypes
v Rowtypes
EndofDynamic Server
Aggregatesareoftenusedtosummarizeinformationaboutgroupsofrowsin atable.ThisuseisdiscussedinChapter5.Whenyouapplyanaggregate functiontoanentiretable,theresultcontains asinglerow thatsummarizesall theselectedrows.
Usingthe COUNTFunction
Figure4-1countsanddisplays thetotalnumberofrowsinthestocktable.
Figure4-3includesaWHEREclausetocountspecific rowsinthestocktable, inthiscase,onlythoserowsthathavea manu_codeofSHM.
SELECT COUNT(*) FROM stock
Figure4-1.Query
(count(*))
73
Byincludingthekeyword DISTINCT(oritssynonymUNIQUE)andacolumn nameinFigure4-5,youcantallythenumberofdifferentmanufacturercodes inthestocktable.
UsingtheAVGFunction
Figure4-7computes theaverageunit_priceofallrowsinthestocktable.
Figure4-9computes theaverageunit_priceofjustthoserowsinthestock tablethathaveamanu_codeofSHM.
SELECT COUNT (*) FROM stock WHERE manu_code = ’SHM’
Figure4-3.Query
(count(*))
17
Figure4-4.QueryResult
SELECT COUNT (DISTINCT manu_code) FROM stock
Figure4-5.Query
(count)
9
Figure4-6.QueryResult
SELECT AVG (unit_price) FROM stock
Figure4-7.Query
(avg)
$197.14
Usingthe MAXandMIN Functions
YoucancombineaggregatefunctionsinthesameSELECTstatement. For example,youcanincludeboththeMAXandtheMINfunctionsintheselect list,asFigure4-11shows.
Figure4-11findsand displaysboththehighestand lowestship_chargeinthe orderstable,asFigure4-12 shows.
Usingthe SUMFunction
Figure4-13 calculatesthetotalship_weightoforders thatwereshippedon July13,1998.
SELECT AVG (unit_price) FROM stock WHERE manu_code = ’SHM’
Figure4-9.Query
(avg)
$204.93
Figure4-10.QueryResult
SELECT MAX (ship_charge), MIN (ship_charge) FROM orders
Figure4-11.Query
(max) (min)
$25.20 $5.00
Figure4-12.QueryResult
SELECT SUM (ship_weight) FROM orders WHERE ship_date = ’07/13/1998’
Usingthe RANGEFunction
TheRANGEfunction computesthedifferencebetweenthemaximumand the minimumvaluesfortheselectedrows.
YoucanapplytheRANGEfunctiononlytonumericcolumns.Figure4-15 findstherangeof pricesforitemsinthestocktable.
Aswithotheraggregates,theRANGEfunctionappliestotherowsofagroup whenthequeryincludesaGROUPBYclause,whichFigure4-17 shows.
(sum)
130.5
Figure4-14.QueryResult
SELECT RANGE(unit_price) FROM stock
Figure4-15.Query
(range)
955.50
Figure4-16.QueryResult
SELECT RANGE(unit_price) FROM stock GROUP BY manu_code
Usingthe STDEVFunction
TheSTDEVfunctioncomputes thestandarddeviationfortheselectedrows. ItisthesquarerootoftheVARIANCEfunction.
YoucanapplytheSTDEVfunction onlyto numericcolumns.Thefollowing queryfindsthestandarddeviationona population:
SELECT STDEV(age) FROM u_pop WHERE age > 21
Aswiththeotheraggregates,theSTDEV functionappliestotherowsofa groupwhenthequeryincludesa GROUPBYclause,asthefollowingexample shows:
SELECT STDEV(age) FROM u_pop GROUP BY state
WHERE STDEV(age) > 21
Nullsareignoredunlesseveryvalueinthespecifiedcolumnisnull.Ifevery columnvalueisnull,theSTDEVfunction returnsa nullforthatcolumn.For moreinformationabouttheSTDEV function,seetheExpressionsegmentin theIBM Informix:GuidetoSQLSyntax.
Usingthe VARIANCEFunction
TheVARIANCEfunctionreturnsthevarianceforasampleofvaluesasan unbiasedestimate ofthevariance forallrowsselected.Itcomputesthe followingvalue:
(SUM(Xi**2) - (SUM(Xi)**2)/N)/(N-1)
Inthis example,Xiiseachvalue inthecolumnand Nisthetotalnumber of valuesinthecolumn.Youcanapply theVARIANCEfunction onlytonumeric columns.Thefollowingqueryfindsthevarianceonapopulation:
SELECT VARIANCE(age) FROM u_pop WHERE age > 21 (range) 820.20 595.50 720.00 225.00 632.50 0.00 460.00 645.90 425.00
Aswiththeotheraggregates,theVARIANCEfunction appliestotherowsof agroupwhenthequeryincludesaGROUPBYclause,whichthefollowing exampleshows:
SELECT VARIANCE(age) FROM u_pop GROUP BY birth
WHERE VARIANCE(age) > 21
Nullsareignoredunlesseveryvalueinthespecifiedcolumnisnull.Ifevery columnvalueisnull,theVARIANCEfunctionreturnsanull forthatcolumn. FormoreinformationabouttheVARIANCEfunction,seetheExpression segmentintheIBM Informix:GuidetoSQLSyntax.
ApplyingFunctionstoExpressions
Figure4-19 showshowyoucanapply functionstoexpressionsand supply displaylabelsfortheirresults.
Figure4-19 findsanddisplays themaximum,minimum,and averageamounts oftime(indays,hours,and minutes)betweenthereceptionandresolutionof acustomercalland labelsthederived valuesappropriately.Figure4-20shows thesequalitiesoftime.