SQL Server Aggregation functions

An aggregate function evaluates a set of values and returns a single value. In addition to the count (number of statistics) function, the other aggregate functions ignore null values (NULL) in the formula. All aggregate functions are deterministic functions. That is, any time a set of identical input values is used to call the return value of the aggregate function after execution is the same, without ambiguity. T-SQL provides a total of 13 aggregate functions.

Aggregate functions are typically used in the following situations:

1. Select a list of selected statements, including sub-queries and external queries.

2. When using compute or compute by to generate a summary column.

3. The HAVING clause filters the data records of the grouping.

Aggregation functions

　　1, average Avg

The AVG function is used to calculate the average of an exact or approximate data type, except for the bit type, ignoring null values. The AVG function calculates the sum of a set of numbers and divides it by the number of NULL to get the average.

Syntax structure:

[] expression)

All: The default value, which means that the average is calculated for the data used.

Distinct: A unique value for each value calculates an average value, regardless of the same value appearing more than once, multiple rows with the same values appear only once as a calculation.

Expressions: An expression of an exact or approximate value. Subqueries and other aggregate functions are not allowed inside an expression.

Example:

AVG (from person – the average of the    age in the person table, the same value is counted only once  

　　2, Min value min

The Min function is used to calculate the minimum value, and the Min function can be applied to numeric, char, varchar, or datetime, money, or smallmoney columns, but not to the bit column. The use of aggregate functions and subqueries is not allowed, and null values are ignored.

Syntax structure:

[] expression)

Example:

From person    --The minimum age to query the person table 

　　3. Max value Max

The Max function is used to calculate the maximum value, ignoring the null value. The Max function can be used for numeric, char, varchar, money, smallmoney, or datetime columns, but not for the bit column. The use of aggregate functions and subqueries is not allowed.

Syntax structure:

[] expression)

Example:

From person    --the maximum value of the age in the Query person table 

　　4, Sum value sum

The SUM function is used for summing, only for exact or approximate numeric type columns (except for bit types), ignores null values, and does not allow aggregation functions and subqueries.

1. Grammatical structure:

[] expression)

Example:

From person    --The sum of the ages in the person table 

　　5, Statistical value count (COUNT_BIG)

The Count function is used to calculate the number of data items that satisfy a condition and returns the value of the int data type.

1. Grammatical structure:

Count ({[[All | distinct*}]  

The expression here is an expression of any data type except text, image, or ntext. The use of aggregate functions and subqueries is not allowed.

2. Common use methods

COUNT (*): Returns all the number of items, including null values and duplicates. In addition to count (*), any other form of the count () function ignores null rows. In addition to the count (*) function, any other aggregate function ignores the null value, that is, if the value in the AVG () parameter is null, this line is ignored as the average calculation.

Count (all expression): Returns the number of items that are not empty.

COUNT (distinct expression): Returns the number of unique non-empty items

The syntax of COUNT_BIG is exactly the same as count, except that the return value is the bigint data type, so that the returned range of values can be much larger than count.

Note: count (field name), if the field name is null, the Count function is not counted. For example, count (name), if name is empty, the result is not counted.

Example:

Count (from person    --The number of items in the person table that are unique and not empty  

　　6, calculate the standard deviation value Stdev

Syntax structure:

[] expression)

The expression here must be a numeric expression that does not allow the use of aggregate functions and subqueries. The value of an expression is an exact or approximate numeric type, but does not include the bit data type. Null values are ignored.

Standard deviation is high school things, forget what to use, when used to learn back.

Example:

From person    --the standard deviation of the age in the person table 

　　7. Calculate Variance var

The Var function is used to calculate the variance of all values in the specified expression.

Syntax structure:

[] expression)

Expression expressions here must be a numeric expression that does not allow the use of aggregate functions and sub-queries. The value of an expression is an exact or approximate numeric type, but does not include a bit data type, and null values are ignored.

The variance is also high school things, forget.

Example:

From person    --The variance of the age in the Query person table 

　　8, Checksum_agg

　　Returns the checksum for each value in the group. Null values are ignored. Checksum_agg can be used to detect changes in a table. The order of the rows in the table does not affect the results of checksum_agg. In addition, the CHECKSUM_AGG function can be used with the DISTINCT keyword and the GROUP by clause. If a value in the expression list changes, the checksum of the list is usually changed. However, in rare cases, the checksum will remain unchanged.

The syntax is as follows:

　　[] expression)

Parameter description:

All: an aggregate function operation on all values. All is the default value.

DISTINCT: Specifies that Checksum_agg returns a unique checksum value.

Expression: an integer representation. the use of aggregate functions and subqueries is not allowed.

SELECT Checksum_agg (Account_age)FromAccountGOUPDATE AccountSET Account_age=30WHERE account_id = 6GOSELECT checksum_agg (account_age)      from account 

The results appear as follows:

Visible as the table changes, the value returned by the system function has changed. This function is being used to detect changes to the table.

　　9, Count_big

Returns the number of items in a group. The usage of COUNT_BIG is similar to the COUNT function. The only difference between the two functions is their return value. Count_big always returns the bigint data type value. COUNT always returns the int data type value.

10, STDEVP 11, GROUPING 12, grouping_id 13, VARP

SQL Server Aggregation functions