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


Comments

Author: Sibtain03 Jul 2011 Member Level: Gold   Points : 0

Will you please elaborate CHECKSUM_AGG() ? Please explain it with a practical use in project.

Author: kirthiga04 Jul 2011 Member Level: Gold   Points : 1

Hai Sibtain,

Practically CHECKSUM_AGG function can be used to detect data changes in a table. This function can only work on integer data type. CHECKSUM_AGG function is very similar to other aggregate functions like SUM or AVG.

For example:


create table atemp(value int,name varchar)
insert into atemp values(5,'a'),(6,'b'),(7,'c')
select CHECKSUM_AGG(value)chk from atemp

when the data is updated in the table CHECKSUM_AGG will changed

update atemp set value=8 where name='b'
select CHECKSUM_AGG(value)chk from atemp

The changes in the table reflects in the CHECKSUM_AGG function. It is used to detect the data changes in a table.

Author: Mohite Mina20 Mar 2014 Member Level: Silver   Points : 4

SQL Aggregate Functions
SQL aggregate functions return a single value, calculated from values in a column.
Useful aggregate functions:
• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum


SELECT AVG(TransQty) avgTransQty
FROM IntTrans

SELECT COUNT(TransQty) COUNTTransQty
FROM IntTrans


SELECT MAX(TransQty) MAXTransQty
FROM IntTrans


SELECT MIN(TransQty) MINTransQty
FROM IntTrans

SELECT SUM(TransQty) SUMTransQty
FROM IntTrans

SELECT COUNT(*) COUNTTransQty
FROM IntTrans



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: