Common functions in T-SQL (1)-Aggregate functions

1. AVG: returns the average value, which may be followed by the over clause.
   • Syntax: AVG([All | distinct]Expression); All performs aggregate function operations on all values. All is the default value. Distinct

   The specified AVG is executed only on the unique instance of each value, regardless of the number of times the value appears.Expression

   Is an exact or approximate value data type (BitExcept for the data type. Aggregate functions and subqueries are not allowed.

   Example:

   Calculate the average number of vacation hours and total number of sick hours used by the Vice President of adventure works cycles. Each aggregate function generates a separate aggregate value for all retrieved rows.

   USE AdventureWorks;GOSELECT AVG(VacationHours)as 'Average vacation hours',     SUM  (SickLeaveHours) as 'Total sick leave hours'FROM HumanResources.EmployeeWHERE Title LIKE 'Vice President%';

   WhenGROUP BYWhen a clause is used together, each aggregate function generates a value for each group, instead of a value for the entire table. The following example generates a summary value for each sales region. The summary shows the average bonuses received by sales staff in each region and the total sales volume of each region for the current year.

   USE AdventureWorks;GOSELECT TerritoryID, AVG(Bonus)as 'Average bonus', SUM(SalesYTD) 'YTD sales'FROM Sales.SalesPersonGROUP BY TerritoryID;GO
2. Checksum: returns the checksum value calculated based on a row or a group of expressions in the table.
   • Syntax: Checksum(* |Expression[,...N])* All columns of the table are calculated. If any column is of a non-comparable data type, checksum returns an error. Non-comparable data types include text, ntext, image, XML, and cursor, and SQL _variant of any of the above types as the base type.ExpressionUnless it is an expression of any type other than the comparable data type.
   • Checksum calculates a hash value called a checksum for its parameter list. This hash value is used to generate a hash index. If the checksum parameter is a column and an index is generated for the calculated checksum value, the result is a hash index. It can be used to perform equivalent searches on columns. Checksum satisfies the following attributes of the hash function: When the equals (=) operator is used for comparison, if the corresponding elements of the two lists have the same type and are equal, the Checksum applied to the list of any two expressions returns the same value. For this definition, the null value of the specified type is compared as equal. If a value in the expression list is changed, the checksum of the list is also changed. However, in rare cases, the checksum will remain unchanged. Therefore, we do not recommend using checksum to detect whether the value is changed unless the application can tolerate occasional loss of changes. Use hashbytes. When the MD5 hash algorithm is specified, hashbytes is much less likely to return the same result for two different inputs than checksum.
   • Example: UseCHECKSUMGenerate a hash index. Add the calculated checksum column to the index table, and then generate an index for the checksum column to generate a hash index.

   -- Create a checksum index.SET ARITHABORT ON;USE AdventureWorks; GOALTER TABLE Production.ProductADD cs_Pname AS CHECKSUM(Name);GOCREATE INDEX Pname_index ON Production.Product (cs_Pname);GO

   Checksum indexes can be used as hash indexes, especially when the columns to be indexed are long character columns, the indexing speed can be improved. Checksum indexes can be used for equivalent searches.

   /*Use the index in a SELECT query. Add a second search condition to catch stray cases where checksums match, but the values are not the same.*/SELECT * FROM Production.ProductWHERE CHECKSUM(N'Bearing Ball') = cs_PnameAND Name = N'Bearing Ball';GO

   Creating an index for a calculated column will be embodied as a checksum column.ProductNameAny changes made to the value will be propagated to the checksum column. You can also directly generate an index for the index column. However, if the key value is long, it is likely that the checksum and index are not executed, or even the regular index.

    
3. Checksum_agg: return the checksum of each value in the group. The null value is ignored.
   • The syntax is the same as that of checksum.
   • Checksum_agg can be used to detect changes in the table. The sequence of rows in the table does not affect the results of checksum_rows. In addition, the checksum_distinct function can be used with the distinct keyword and the group by clause. If a value in the expression list is changed, the checksum of the list is also changed. However, in rare cases, the check value remains unchanged.
   • Example: UseCHECKSUM_AGGDetectionAdventureWorksDatabaseProductInventoryTableQuantityColumn changes.

   --Get the checksum value before the column value is changed.USE AdventureWorks;GOSELECT CHECKSUM_AGG(CAST(Quantity AS int))FROM Production.ProductInventory;GOUPDATE Production.ProductInventory SET Quantity=125WHERE Quantity=100;GO--Get the checksum of the modified column.SELECT CHECKSUM_AGG(CAST(Quantity AS int))FROM Production.ProductInventory;

4. Count: the number of items in the returned group.
   • Syntax: Count({[[All | distinct]Expression] |*})All performs aggregate function operations on all values. All is the default value. Distinct

   Specify the number of unique non-null values returned by count.ExpressionDivisionText,ImageOrNtextExpressions of any other types. Aggregate functions and subqueries are not allowed.

   Specify that all rows should be calculated to return the total number of rows in the table. Count (*) And cannot be used with distinct. Count (*) Not requiredExpressionParameter, because according to the definition, this function does not use information about any specific column. Count (*) Returns the number of rows in the specified table without deleting the copy. It counts each row separately. Contains rows that contain null values.

   Example: lists the number of titles that an employee can have at adventure works cycles.

   Use adventureworks; goselect count (distinct title) from HumanResources. employee; go calculates the total number of employees of adventure works cycles. Use adventureworks; goselect count (*) from HumanResources. employee; gouse adventureworks; goselect count (*), AVG (bonus) from sales. salespersonwhere salesquota> 25000; go
5. Count_big: return the number of items in the group. The user and count function class are. Count_big returns the bigint data type value. Count returns the int data type value.
   • Returns the number of items in the group. The count function is similar to the count_big function. The only difference between the two functions is their return values. Count always returnsIntData Type value. Count_big always returnsBigintData Type value. It can be followed by the over clause.
6. MAX: returns the maximum value of the expression.
   • Syntax: Max([All | distinct]Expression)
   • Example: return the highest (maximum) Tax Rate

   USE AdventureWorks;GOSELECT MAX(TaxRate)FROM Sales.SalesTaxRate;GO

7. Min: returns the minimum value of the expression.
8. Sum: returns the sum of all values in the expression or the sum of only non-repeating values. Sum can only be used for numeric columns. The null value is ignored.
   • Syntax: Sum([All | distinct]Expression)
   • Example: shows the difference between an aggregate function and a row aggregate function. The first example shows the aggregate function that only provides summary data, and the second example shows the row aggregate function that provides detailed data and summary data.

   USE AdventureWorks;GOSELECT Color, SUM(ListPrice), SUM(StandardCost)FROM Production.ProductWHERE Color IS NOT NULL     AND ListPrice != 0.00     AND Name LIKE 'Mountain%'GROUP BY ColorORDER BY Color;GO
   USE AdventureWorks;GOSELECT Color, ListPrice, StandardCostFROM Production.ProductWHERE Color IS NOT NULL     AND ListPrice != 0.00     AND Name LIKE 'Mountain%'ORDER BY ColorCOMPUTE SUM(ListPrice), SUM(StandardCost) BY Color;GO
   ForProductCalculation of each color listed in the tableListPriceAndStandardCostAnd.
   USE AdventureWorks;GOSELECT Color, SUM(ListPrice), SUM(StandardCost)FROM Production.ProductGROUP BY ColorORDER BY Color;GO