Chittibabu

Aggregate functions return a single value summarizing a given data set. All aggregate functions are deterministic. NOTE: AVG, SUM, STDEV, STDEVP, VAR and VARP functions cannot operate on BIT data types; they can operate on all other numeric data types.

Contents [hide] 1 COUNT Function 2 COUNT_BIG Function 3 MAX Function 4 MIN Function 5 AVG Function 6 SUM Function 7 STDEV Function 8 STDEVP Function 9 VAR Function 10 VARP Function 11 CHECKSUM_AGG Function 12 CHECKSUM Function 13 GROUPING Function 14 More SQL Server Functions

COUNT Function

The COUNT function returns a count of rows based on certain criteria. The syntax is:
Keyword ALL is optional and is assumed by default.
If you specify a "*" as the criterion, COUNT returns the total number of rows in a table; for example, the following query counts rows in the FactFinance table of Adventure Works DW database:

If you join multiple tables then COUNT(*) returns the number of rows satisfying the join criterion, as in the following:
COUNT(*) cannot be used with DISTINCT; nor can you specify any other parameter - this variation of the function automatically counts every single row in a single or multiple joined tables.
Unlike all other aggregate functions, COUNT does not ignore NULL values.
If you need to find the count of unique items within a column in a table use COUNT (DISTINCT column_name) syntax. For example, the following query counts unique organization keys within the FactFinance table:

COUNT_BIG Function

The COUNT_BIG function is identical to the COUNT function, but returns a BIGINT data type, whereas COUNT returns an INT. The upper limit for INT data type is 2 billion; therefore if you anticipate counting more than two billion rows use the COUNT_BIG function. Any attempt to use the COUNT function when counting over 2 billion rows returns an error.

MAX Function

The MAX function returns the biggest value within a given set. The syntax is:

The ALL keyword is optional and is the default unless DISTINCT is specified. The DISTINCT keyword specifies that each unique value should be considered. This keyword really has no use with MAX function since it returns a single value; however the keyword is supported for ANSI compatibility.


For example, the following returns the greatest amount from the FactFinance table:

Note that the parameter of MAX can be any valid expression, including string columns, as in the following:
"Women's Tights, S" is returned because it is the last value in the alphabetically ordered list of product names, not because it is the longest product name. You can combine MAX function with LEN function to return the products with the longest names, as follows:

MIN Function

The MIN function returns the biggest value within a given set. The syntax is:

The ALL keyword is optional and is the default unless DISTINCT is specified. The DISTINCT keyword specifies that each unique value should be considered. This keyword really has no use with MIN function since it returns a single value; however the keyword is supported for ANSI compatibility.

For example, the following returns the greatest amount from the FactFinance table:

Note that the parameter of MIN can be any valid expression, including string columns, as in the following:

Note that "Adjustable Race" is returned because it is the first value in the alphabetically ordered list of product names, not because it is the shortest product name. You can combine MIN function with LEN function to return the products with the shortest names, as follows:

AVG Function

The AVG function returns the average of the values within a column. Unlike MIN and MAX, AVG can only accept a numeric expression as a parameter. The syntax is:

ALL keyword is optional and is assumed by default. You can specify the DISTINCT keyword if you want to take into account only the unique occurrence of each value.
The following query returns the average amount from FactFinance table:

The next example uses DISTINCT keyword to return the average of distinct values:

The AVG function returns the same (or a similar) data type as the group of values it accepts. So if you pass an integer, expect an integer back; if you examine float values with the AVG function you will get a float value back.

SUM Function

The SUM function returns the sum of all or unique values. Unlike MIN and MAX, SUM can only accept a numeric expression as a parameter. The syntax is:

ALL keyword is optional and is assumed by default. You can specify the DISTINCT keyword if you want to take into account only the unique occurrence of each value.


The following query returns the sum of amounts from FactFinance table:

The next example uses DISTINCT keyword to return the sum of distinct values:

The SUM function returns the same (or a similar) data type as the group of values it accepts. So if you pass an integer, expect an integer back; if you examine float values with the SUM function you will get a float value back.

STDEV Function

The STDEV function calculates the standard deviation for all items in the SELECT statement. This function can only be used with numeric columns. The syntax is:

ALL keyword is optional and is assumed by default. You can specify the DISTINCT keyword if you want to take into account only the unique occurrence of each value. The STDEV function always returns a FLOAT data type value.


The following example returns the standard deviation of amounts from FactFinance table:

STDEVP Function

The STDEVP function calculates the standard deviation for the population of items in the SELECT statement. This function can only be used with numeric columns. The syntax is:

ALL keyword is optional and is assumed by default. You can specify the DISTINCT keyword if you want to take into account only the unique occurrence of each value. The STDEVP function always returns a FLOAT data type value.


The following example returns the standard deviation of population for amounts in the FactFinance table:

VAR Function

The VAR function calculates the statistical variance of all values in the SELECT statement. This function can only be used with numeric columns. The syntax is:

ALL keyword is optional and is assumed by default. You can specify the DISTINCT keyword if you want to take into account only the unique occurrence of each value. The VAR function always returns a FLOAT data type value.


The following example returns the variance of amounts from FactFinance table:

VARP Function

The VARP function calculates the statistical variance for the population of values in the SELECT statement. This function can only be used with numeric columns. The syntax is:

ALL keyword is optional and is assumed by default. You can specify the DISTINCT keyword if you want to take into account only the unique occurrence of each value. The VARP function always returns a FLOAT data type value.


The following example returns the variance for the population of amounts from the FactFinance table:

CHECKSUM_AGG Function

The CHECKSUM_AGG function can be used to detect data changes in a table. This function can only work on integer data type; otherwise the functionality is very similar to other aggregate functions like SUM or AVG. The syntax is:

ALL keyword is optional and is assumed by default. You can specify the DISTINCT keyword if you want to take into account only the unique occurrence of each value. The CHECKSUM_AGG function always returns a INT data type value.


The following query returns the checksum of amount column in FactFinance table; then it updates some rows in the table; the second checksum is different from the first indicating that the data values have changed:
Results:

CHECKSUM Function

The CHECKSUM function returns the checksum value computed over a row in a given table; alternatively it can return the checksum of the specified list of values. This function is intended for building hash indexes and always returns an INT data type value. The syntax is:

The CHECKSUM(*) flavor examines all columns of a given table; it returns an error if the table contains TEXT, NTEXT or IMAGE data type columns. If you specify expressions instead of a table then expressions must be of any data type other than TEXT, NTEXT, IMAGE, CURSOR or sql_variant containing one of the preceding data types.


The following example returns a CHECKSUM of the first row in the DimProduct table:

The next example returns a CHECKSUM of given values:

To add a hash index to a table you need to add a computed column using CHECKSUM function and then create an index on the computed column, as in the following:

GROUPING Function

The GROUPING function returns a value of 1 if the row is added to the result set by either ROLLUP or CUBE clause of the SELECT statement. CUBE and ROLLUP extensions allow generating very simple textual reports in a query window. Majority of business reports will use more sophisticated reporting tools than the Query Analyzer or SQL Server Management Studio, therefore CUBE and ROLLUP extensions, as well as the GROUPING function have limited usage. The syntax of the GROUPING function is:

The column is a column referred to in the GROUP BY statement.


The following query returns sum of amounts per organization and total of all organization amounts combined as a separate "grouping row":

The ROLLUP extension and the GROUPING function are more useful when the report is grouping records based on multiple columns. For example, the following query groups the output by product class, style and English name:
Note that a grouping row is generated for each class, each class and style combination and the grand total of all sales.