# Aggregate Functions in Sql Server

In this article, I explain the purpose of using Aggregate Functions with relevant Examples. Some of Aggregate Functions explained are AVG; CHECKSUM_AGG; COUNT; COUNT_BIG; GROUPING; MAX; MIN; SUM; STDEV; STDEVP; VAR; VARP;

**AGGREGATE FUNCTIONS:****AVG():** Returns the average of the values in a group.

Example:

AVG with DISTINCT:

select AVG(DISTINCT price) as Average from sales

AVG without DISTINCT:

select AVG(marks) as Average from student

**CHECKSUM_AGG():**Returns the checksum of the values in a group.

Example:

select subject, CHECKSUM_AGG(marks) as checksum from student group by subject

**COUNT():**

Returns the number of items in a group. COUNT always returns an int data type value.

Example:

Using COUNT(*):

select COUNT(*) as Total_count from student

Using COUNT and DISTINCT:

select COUNT(DISTINCT Name) as Total_student from student

**COUNT_BIG():**Returns the number of items in a group. COUNT_BIG works like the COUNT function. The only difference between the two functions is their return values. COUNT_BIG always returns a bigint data type value.

Example:

select COUNT_BIG(*) as Total_count from student

**GROUPING():**Indicates whether a specified column expression in a GROUP BY list is aggregated or not. GROUPING returns 1 for aggregated or 0 for not aggregated in the result set. GROUPING can be used only in the SELECT <select> list, HAVING, and ORDER BY clauses when GROUP BY is specified.

Example:

select name, GROUPING(marks) as Group from student group by name, marks

**MAX():**Returns the maximum value in the expression.

Example:

select MAX(marks) as First_mark from student

**MIN():**Returns the minimum value in the expression.

Example:

select MIN(marks) as Last_mark from student

**SUM():**Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only.

Example:

select SUM(marks) as Total from student

**STDEV():**Returns the statistical standard deviation of all values in the specified expression.

Example:

select STDEV(price) as SD from sales

**STDEVP():**Returns the statistical standard deviation for the population for all values in the specified expression.

Example:

select STDEVP(price) as SDP from sales

**VAR():**Returns the statistical variance of all values in the specified expression.

Example:

select VAR(price) as Variance from sales

**VARP():**Returns the statistical variance for the population for all values in the specified expression.

Example:

select VARP(price) as VRP from sales

GoldPoints : 0