Table of Contents |

## 4.2 ANSI SQL Aggregate FunctionsAggregate
functions return a single value based
upon a set of other values. If used among other expressions in the
item list of a Technically speaking, The number of values processed by an aggregate function varies depending on the number of rows queried from the table. This behavior makes aggregate functions different from scalar functions, which can only operate on the values of a single row per invocation. The general syntax of an aggregate function is:
The aggregate function name may be
The ## SQL Standard SyntaxAVG ([ALL | DISTINCT] ## MySQL, PostgreSQL, and SQL ServerAll these platforms support the SQL2003 syntax of
## DB2 and OracleDB2 and Oracle support the ANSI syntax and the following analytic syntax: AVG ([ALL | DISTINCT] For an explanation of the ## ExamplesThe following query computes average year-to-date sales for each type of book:
This query returns the sum of year-to-date sales for each type of book:
The ## SQL2003 SyntaxCalls the function with two variables, one dependent and the other independent: CORR( Any pair in which either the dependent variable, independent variable, or both are NULL is ignored. The result of the function is NULL when none of the input pairs consist of two non-NULL values. ## OracleOracle supports the SQL2003 syntax, and the following analytic syntax: CORR ( For an explanation of the ## DB2, MySQL, PostgreSQL, and SQL ServerThese platforms do not support any form of the
## ExampleThe following
The ## SQL2003 SyntaxCOUNT(*) COUNT( [ALL|DISTINCT] *COUNT*(*)Counts all the rows in the target table whether or not they include NULLs. *COUNT( [ALL|DISTINCT] expression)*Computes the number of rows with non-NULL values in a specific column or expression. When the keyword *DISTINCT*is used, duplicate values will be ignored and a count of the distinct values is returned.*ALL*returns the number of non-NULL values in the expression and is implicit when*DISTINCT*is not used.
## MySQL, PostgreSQL, and SQL ServerAll of these platforms support the SQL2003 syntax of
## DB2 and OracleDB2 and Oracle support the ANSI syntax and the following analytic syntax: COUNT ({*|[DISTINCT] For an explanation of the ## ExamplesThis query counts all rows in a table: SELECT COUNT(*) FROM publishers; The following query finds the number of different countries where publishers are located: SELECT COUNT(DISTINCT country) "Count of Countries" FROM publishers
The ## SQL2003 SyntaxCall the function with two variables, one dependent and the other independent: COVAR_POP( The function disregards any pair in which either the dependent variable, independent variable, or both are NULL. If no rows remain in the group after NULL elimination, then the result of the function is NULL. ## OracleOracle supports the SQL2003 syntax and implements the following analytic syntax: COVAR_POP ( For an explanation of the ## DB2In DB2, the function is named ## MySQL, PostgreSQL, and SQL ServerThese platforms do not support any form of the
## ExampleThe following
The ## SQL2003 SyntaxCall the function with two variables, one dependent and the other independent: COVAR_SAMP( The function disregards any pair in which either the dependent variable, independent variable, or both are NULL. The result of the function is NULL when none of the input pairs consist of two non-NULL values. ## OracleOracle supports the SQL2003 syntax and implements the following analytic syntax: COVAR_SAMP ( For an explanation of the ## DB2, MySQL, PostgreSQL, and SQL ServerThese platforms do not support any form of the
## ExampleThe following
Computes the relative rank of a hypothetical row within a group of rows, where that relative rank is computed as follows: ( Bear in mind that the ## SQL2003 SyntaxIn the following syntax, items in the
. Therefore, both lists
must have the same number of expressions.sort_listCUME_DIST( ## OracleOracle follows the SQL2003 syntax and implements the following analytic syntax: CUME_DIST OVER ( For an explanation of the clauses, see the section later in
this chapter titled Section 4.3.order## DB2, MySQL, PostgreSQl, and SQL ServerThese platforms do not implement the CUME_DIST aggregate function. ## ExampleThe following example determines the relative rank of the
hypothetical new row
In group (3 rows preceding + 1 peering) / (3 in group + 1 hypothetical) = 4 / 4 = 1 In group (3 rows preceding + 1 peering) / (4 in group + 1 hypothetical) = 4 / 5 = .8
Computes a rank in a group for a hypothetical row that you supply. This is a dense rank. Rankings are never skipped, even when a group contains rows that rank identically. ## SQL2003 SyntaxIn the following syntax, items in the
. Therefore, both lists
must have the same number of expressions.sort_listDENSE_RANK( ## OracleOracle follows the SQL2003 syntax and implements the following analytic syntax: DENSE_RANK( ) OVER ( For an explanation of the clauses, see the section later in
this chapter titled Section 4.3.order## DB2, MySQL, PostgreSQl, and SQL ServerThese platforms do not implement the DENSE_RANK aggregate function. However, DB2 does support DENSE_RANK as an analytic function. See the section later in this chapter titled Section 4.3. ## ExampleThe following example determines the dense rank of the hypothetical
new row
In group
DISTINCT or
ALL may be used with these functions, but do not
affect the result.## SQL2003 SyntaxMIN( [ALL | DISTINCT] ## PostgreSQL and SQL ServerThese platforms support the SQL2003 syntax of
## DB2 and OracleDB2 and Oracle support the ANSI syntax and implements the following analytic syntax: MIN ({ALL|[DISTINCT] For an explanation of the ## MySQLMySQL supports the SQL2003 syntax of ## ExamplesThe following query finds the best and worst sales for any title on record: SELECT MIN(ytd_sales), MAX(ytd_sales) FROM titles; Aggregate functions are used often in the SELECT type 'Category', AVG( price ) 'Average Price' FROM titles GROUP BY type HAVING AVG(price) > 15
Generates a relative rank for a hypothetical row by dividing that row's rank less 1 by the number of rows in the group. ## SQL2003 SyntaxIn the following syntax, items in the
. Therefore, both lists
must have the same number of expressions.sort_listPERCENT_RANK( ## OracleOracle follows the SQL2003 syntax and implements the following syntax: PERCENT_RANK( ) OVER ([ For an explanation of the clauses, see the section later in
this chapter titled Section 4.3.order## DB2, MySQL, PostgreSQl, and SQL ServerThese platforms do not implement the
## ExampleThe following example determines the percentage rank of the
hypothetical new row
In group
Generates an interpolated value corresponding to a percentile that you specify. ## SQL2003 SyntaxIn the following syntax, PERCENTILE_CONT( ## OracleOracle allows only one expression in the PERCENTILE_CONT( Oracle also allows some use of windowing syntax: PERCENTILE_CONT ( See Section 4.3 later in this chapter for a description of partitioning. ## DB2, MySQL, PostgreSQL, and SQL ServerThese platforms do not implement ## ExampleThe following example groups the data in test4 by the column named odd, and invokes
Determines the value in a group with the smallest cumulative distribution greater than or equal to a percentile that you specify. ## SQL2003 SyntaxIn the following syntax, PERCENTILE_DISC( ## OracleOracle allows only one expression in the ORDER BY clause: PERCENTILE_DISC( Oracle also allows some use of windowing syntax: PERCENTILE_DISC ( See Section 4.3 later in this chapter for a description of partitioning. ## DB2, MySQL, PostgreSQL, and SQL ServerThese platforms do not implement ## ExampleThe following example is similar to that for
PERCENTILE_CONT, except that it returns, for
each group, the value closest, but not exceeding, the
60
Computes a rank in a group for a hypothetical row that you supply. This is not a dense rank. If the group contains rows that rank identically, then it's possible for ranks to be skipped. If you want a dense rank, use the DENSE_RANK function. ## SQL2003 Syntax
. Therefore, both lists
must have the same number of expressions.sort_listRANK( ## OracleOracle follows the SQL2003 syntax and implements the following analytic syntax: RANK( ) OVER ([
clauses, see the section later in
this chapter titled Section 4.3.order## DB2, MySQL, PostgreSQl, and SQL ServerThese platforms do not implement the ## ExampleThe following example determines the rank of the hypothetical new row
In both cases, the rank of the hypothetical new row is 4. In group
SQL2003 defines a family of functions, having names beginning with
## SQL2003 SyntaxFollowing is the syntax and a brief description of each
- REGR_AVGX(
,*dependent*)*independent* Averages (as in *AVG(**x**)*) thevariable values.*independent*- REGR_AVGY(
,*dependent*)*independent* Averages (as in *AVG(**y**)*) thevariable values.*dependent*- REGR_COUNT(
,*dependent*)*independent* Counts the number of non-NULL number pairs. - REGR_INTERCEPT(
,*dependent*)*independent* Computes the y-intercept of the regression line. - REGR_R2(
,*dependent*)*independent* Computes the coefficient of determination. - REGR_SLOPE(
,*dependent*)*independent* Computes the slope of the regression line. - REGR_SXX(
,*dependent*)*independent* Sums the squares of the independent variable values. - REGR_SXY(
,*dependent*)*independent* Sums the products of each pair of values. - REGR_SYY(
,*dependent*)*independent* Sums the squares of the dependent variable values.
The ## DB2 and OracleDB2 and Oracle support the SQL2003 syntax for all
Oracle supports the following analytic syntax: REGR_ For an explanation of the ## MySQL, PostgreSQL, and SQL ServerThese platforms do not implement the ## ExampleThe following
The
Likewise, all other
Use STDDEV_POP to find the population standard deviation within a group of numeric values. ## SQL2003 SyntaxSTDDEV_POP( ## DB2 and MySQLUse the ## PostgreSQLThis platform does not provide a function to compute population standard deviation. ## OracleOracle supports the standard syntax and the following analytic syntax: STDDEV_POP ( For an explanation of the ## SQL ServerUse the ## ExampleThe following example computes the population standard deviation for the values 1, 2, and 3:
Use ## SQL2003 SyntaxSTDDEV_SAMP( ## OracleOracle supports the standard syntax. Oracle also provides the
Oracle also supports analytic syntax: STDDEV_SAMP ( For an explanation of the ## DB2This platform does not provide a function to compute sample standard deviation. ## MySQLMySQL does not provide a function to compute sample standard
deviation. MySQL does provide a function named
## PostgreSQLUse ## SQL ServerUse ## ExampleThe following example computes the sample standard deviation for the values 1, 2, and 3:
Use ## SQL2003 SyntaxVAR_POP( ## DB2 and PostgreSQLThese platforms do not provide a function to compute population variance. ## MySQLUse the ## OracleOracle supports the standard syntax and the following analytic syntax: VAR_POP ( For an explanation of the ## SQL ServerUse the ## ExampleThe following example computes the population variance for the values 1, 2, and 3:
Use ## SQL2003 SyntaxVAR_SAMP( ## DB2 and PostgreSQLUse VARIANCE ## MySQLMySQL provides no function for computing sample variance. There is
the ## OracleOracle supports the standard syntax. You may also use the
Oracle also supports analytic syntax: VAR_SAMP ( For an explanation of the ## SQL ServerUse the ## ExampleThe following example computes the sample variance for the values 1, 2, and 3:
1 |

Table of Contents |