Aggregate functions in sql server?

AGGREGATE FUNCTIONS IN SERVER

Aggregations will perform calculations on a set of values and returns a

 single value.

These are aggregate fns in sql server

AVG, 

MIN, 

CHECKSUM_AGG,

 SUM,

 COUNT,

 STDEV, 

COUNT_BIG,

 STDEVP,

 GROUPING, 

VAR,

 MAX,

 VARP.

Examples

AVG:  select AVG(Sales) from Sales as averagesalary

Max: select MAX(Sales) from Sales as maximumsalary

Min:select Min(Sales) from Sales

COUNT:select COUNT(*) from Sales

stdev

CHECKSUM_AGG

select CHECKSUM_AGG(CAST(Sales as int)) from Sales

Explanation:

CHECKSUM_AGG function is used to get checksum of values in a group. It ignores null values in computation.

CHECKSUM_AGG can be used along with BINARY_CHECKSUM to detect changes in a table.

The order of the rows in the table does not affect the result of CHECKSUM_AGG. In addition, CHECKSUM_AGG functions may be used with the DISTINCT keyword and the GROUP BY clause.

Syntax of CHECKSUM function :

CHECKSUM ( * | expression [ ,...n ] )

* means that the computation is over all the columns of the table.

Expression is any valid sql expression of any type.

 Example of CHECKSUM function :

Example 1 : Use of CHECKSUM_AGG function in SELECT clause to get checksum in group

SELECT   CHECKSUM_AGG(CONVERT(INT,UnitsInStock))

FROM   Products

Output

 50

Now update some of the rows and again run the query.

UPDATE Products

SET         UnitsInStock = 15

WHERE  UnitsInStock < 10

  

SELECT  CHECKSUM_AGG(CONVERT(INT,UnitsInStock))

FROM     Products

Output

61

Above example displays checksum of unit price field in the products table.

 Example 2 : Using CHECKSUM_AGG function with BINARY_CHECKSUM to detect changes in a table

SELECT  CHECKSUM_AGG(BINARY_CHECKSUM(UnitsInStock))

FROM     Products

Output

7913472

Above example displays checksum to detect changes in the unit in stock field of product table.

COUNTBIG;

COUNT() aggregate function is used in SQL to calculate the total count of items in a group.
COUNT_BIG() does the same calculation as COUNT() but difference is what they return.
The difference between the two is COUNT_BIG() returns a BigInt data type value And COUNT() return an Int data type.

Count calculates all null values as well in the group count calculation. All the other aggregate functions may exclude the null values in the calculations.

Examples,

Code:

select COUNT(Distinct city) from tablepeople

If you have millions of rows in a table, COUNT() may throw an error message so you can use COUNT_BIG function instead to avoid this data type error.

Code:

select COUNT_BIG(id) from tablepeople

VAR: