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
Will you please elaborate CHECKSUM_AGG() ? Please explain it with a practical use in project.