T-SQL aggregate functions

--This article is for study notes, ("T_sql" Robert Sheldon Translator: Feng Yu Hui Source: TT China)

T-SQL aggregate functions

Basic knowledge

--In SQL Server 2008, Transact-SQL has some aggregate functions that enable you to perform such calculations. A T-SQL aggregate function returns a value that can be used as an expression in a Select query list or "COMPUTE", "COMPUTE by" or "having" clauses. All aggregate functions ignore the "null" value, except for the "COUNT" function.

Select Territord_id,avg (Subtotal) as avgsales,sum (subtotal) as Toatalsales from Sales.SalesOrderHeader Group by territory_id ORDER BY territory_id

--the example groups the values by the Territory ID column, and then averages each grouping that is generated and calculates the total sales based on the "SubTotal" column

--Two additional aggregate functions: the "MAX" function and the "MIN" function. The "Max" function returns the maximum value in a set of values, while the "Min" function returns the minimum value. Both functions ignore the "null" value. When you work with character data, the maximum and minimum values are calculated based on the natural order of the columns. Of course, you can also use the "MAX" function and the "MIN" function for character and date data. For example, the "name" column is specified as the "nvarchar" data type. When I use the "max" function or the "min" function for this column, the maximum and minimum values returned are based on the alphabetical order of the column names, and the "min" function or the "MAX" function is used for datetime columns, and the returned value is calculated based on the earliest value or the latest value of the DateTime value saved in the column.

Number of rows calculated

The "COUNT" function returns the number of values for a set. Unlike other aggregate functions, you can specify whether to include a "null" value when counting. In addition, you can specify whether to calculate all values or just different values

Select COUNT (*) as ProductCount the number of rows,--the collection, including null values

Count (ProductModelID) as Modelcouont the number of Rows,--column ProductModelID, with no null value

Count (discinct ProductModelID) as DistinctCount--column ProductModelID number of rows not duplicated, non-null value

From Production.Product

--Note: Most of the aggregate functions support the "DISTINCT" keyword, so you can specify that the aggregate function executes only in data that is not duplicated in the specified group. For more information, refer to the SQL Server Books Online section on how to use "DISTINCT" in each function.

--the "COUNT" function returns the "int" type value, "Count_big" returns the "bigint" value.

Manipulating grouped data

--When using a "GROUP by" clause in a T-SQL statement, you can use "ROLLUP", "CUBE" or "gourping sets" operators to specify additional statistics contained in the result set. This part of the summary data is represented as a "null" value in the "GROUP by" list. This is a special use of the "null" value.

--However, the column specified in the "GROUP by" column can contain a "null" value, which in this case is also displayed as "null" in the result of the column.

--to differentiate between these two types of "null" values, you can use the "GROUPING" aggregate function provided by SQL Server. The function can explicitly specify whether the column specified in the "GROUP by" clause participates in the collection calculation. If the value in the result set participates in the collection operation, the "GROUPING" function returns the value "1". Otherwise, the function returns a value of "0"

Select Color,

AVG (ListPrice) as Avgprice,count (*) as TotalAmount,

grouping (color) as Agggroup--the "grouping" function is often used to indicate whether the value displayed in a column is a grouping value or a cumulative value that participates in a set operation

From Producttion.product GROUP by color with rollup

--the "GROUP by" clause has a "with ROLLUP" operator. Because "Color" is the column specified in the "GROUP by" clause, the column can be used in the "GROUPING" function in the "select" list.

--the "GROUPING" function can only be used in the "select" list, "having" clauses or "ORDER by" clauses.

Verifying the sum checksum

--sql Server also provides the "Checksum_agg" function, which supports running a sum check on a set of values. This is handy when detecting changes in a table's data. Note, however, that the sum check can only be used on an integer expression, and the "null" value is ignored when performing the calculation.

Select Checksum_agg (CAST (quantity as int)) as Checksumagg,--return 262

Checksum_agg (distinct cast (quantity as int)) as Checksumdistinct--return 78

From Production.productinventory

--Because the Quantity column of table ProductInventory is configured with a data type of smallint, it must be converted to the data type of int before running the sum check.

Get statistics

SQL Server has four aggregate functions that allow you to do data statistics on a set of values for a numeric column:

STDEV: The statistical standard variance of the return value.

STDEVP: Returns the statistical standard variance of the population value.

VAR: The statistical variance of the return value.

VARP: Returns the statistical variance of the population value.

--Each of these functions ignores the "null" value and the return value is a floating-point type value. The return value indicates the degree to which the group value deviates from the average (the standard variance is the square root of the variance). I'm sorry, I'm not a professional statistic, so if you want to learn more about how to calculate the standard variance and variance (and how well the statistical population fits into those calculations), you'll have to look for a statistical book. At the same time, the following statements illustrate the use of these functions: select Stdev (ListPrice) as STDEVPRICE,STDEVP (ListPrice) as Stdevpprice,var (ListPrice) as Varprice,varp (ListPrice) as Varpprice from Production.Product

This article is from the "Holly" blog, make sure to keep this source http://5968067.blog.51cto.com/5958067/1698873

T-SQL aggregate functions