SQL Server T-SQL Aggregate Functions

By:   |   Comments (2)   |   Related: > Functions System


Problem

I have started a new application where I need to start aggregating some of the data and perform some basic mathematical functions. I know I can perform some of the calculations in the front end application, but I am not sure this is the best approach in the long term. How can I do perform the aggregations in my T-SQL code based on some user needs in the OLTP application and in the corresponding reporting application? What aggregate functions are available in SQL Server 2000 and 2005? Can you provide me with some examples on how to do so in my T-SQL code?

Solution

SQL Server 2000 and 2005 ship with a number of functions that can be called directly in your T-SQL code without having to build any additional code. So these functions should be able to be incorporated into your OLTP and Reporting application directly in your T-SQL code. Specifically for SQL Server 2000 and 2005, the following aggregate functions are available:

ID Description SQL Server 2000 SQL Server 2005
1 Average - Returns the average of the values in the select list ignoring the NULL values. SELECT AVG(YTD_Sales)
FROM Pubs.dbo.titles WHERE type <> 'business'
GO
SELECT AVG(VacationHours)
FROM AdventureWorks.HumanResources.Employee;
GO
2 BINARY_CHECKSUM - The checksum as a binary value for a single row or for particular columns in a table. SELECT TitleID, BINARY_CHECKSUM(*)
FROM Pubs.dbo.titles GO
SELECT EmployeeID, BINARY_CHECKSUM(*)
FROM AdventureWorks.HumanResources.Employee;
GO
3 CHECKSUM - The checksum as a integer value for a single row or for particular columns in a table. SELECT TitleID, CHECKSUM(*)
FROM Pubs.dbo.titles GO
SELECT EmployeeID, CHECKSUM(*)
FROM AdventureWorks.HumanResources.Employee;
GO
4 CHECKSUM_AGG - Returns the checksum of the values in a table as an integer. SELECT CHECKSUM_AGG(*)
FROM Pubs.dbo.titles GO
SELECT CHECKSUM_AGG(*)
FROM AdventureWorks.HumanResources.Employee;
GO
5

COUNT - Returns the number of items in the select list as an integer data type including NULL and duplicate values.

SELECT COUNT(*)
FROM Pubs.dbo.titles
GO
SELECT COUNT(*)
FROM AdventureWorks.HumanResources.Employee;
GO
6

COUNT_BIG - Returns the number of items in the select list as a big integer data type including NULL and duplicate values.

SELECT COUNT_BIG(*)
FROM Pubs.dbo.titles GO
SELECT COUNT_BIG(*)
FROM AdventureWorks.HumanResources.Employee;
GO
7 DISTINCT - Not include duplicate values in the SELECT list. SELECT DISTINCT(Titles)
FROM Pubs.dbo.titles WHERE type <> 'business'
GO
SELECT DISTINCT(VacationHours)
FROM AdventureWorks.HumanResources.Employee;
GO
8 GROUPING - The GROUPING aggregate is always used with a GROUP BY and either the ROLLUP or CUBE function to calculate the group's value. SELECT Royalty, SUM(Advance) 'Total Advance', GROUPING(Royalty) 'GRP_Royalty'
FROM Pubs.dbo.Titles
GROUP BY royalty WITH ROLLUP
GO
SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD', GROUPING(SalesQuota) AS 'Grouping'
FROM AdventureWorks.Sales.SalesPerson
GROUP BY SalesQuota WITH ROLLUP;
GO
9 MAX - The highest value in the SELECT list. SELECT MAX(YTD_Sales)
FROM Pubs.dbo.titles WHERE type <> 'business'
GO
SELECT MAX(VacationHours)
FROM AdventureWorks.HumanResources.Employee;
GO
10 MIN - The lowest value in the SELECT list. SELECT MIN(YTD_Sales)
FROM Pubs.dbo.titles WHERE type <> 'business'
GO
SELECT MIN(VacationHours)
FROM AdventureWorks.HumanResources.Employee;
GO
11 SUM - The sum of all the values in the SELECT list which are numeric data types ignoring the NULL values. SELECT SUM(YTD_Sales)
FROM Pubs.dbo.titles WHERE type <> 'business'
GO
SELECT SUM(VacationHours)
FROM AdventureWorks.HumanResources.Employee;
GO
12 STDEV - The standard deviation for all of the values in the SELECT list. SELECT STDEV(YTD_Sales)
FROM Pubs.dbo.titles WHERE type <> 'business'
GO
SELECT STDEV(VacationHours)
FROM AdventureWorks.HumanResources.Employee;
GO
13 STDEVP - The standard deviation for the population for all values in the SELECT list. SELECT STDEVP(YTD_Sales)
FROM Pubs.dbo.titles WHERE type <> 'business'
GO
SELECT STDEVP(VacationHours)
FROM AdventureWorks.HumanResources.Employee;
GO
14 VAR - The variance of the population for all values in the SELECT list. SELECT VAR(YTD_Sales)
FROM Pubs.dbo.titles WHERE type <> 'business'
GO
SELECT VAR(VacationHours)
FROM AdventureWorks.HumanResources.Employee;
GO
15 VARP - The variance of the population for all values in the SELECT list. SELECT VARP(YTD_Sales)
FROM Pubs.dbo.titles WHERE type <> 'business'
GO
SELECT VARP(VacationHours)
FROM AdventureWorks.HumanResources.Employee;
GO
Next Steps
  • Based on the application needs, these native functions may be able to resolve some or all of your requirements in a familiar and efficient programming language when working with data in a SQL Server database.
  • If the native functions do not meet your needs, then another alternative is to build user defined functions in T-SQL that can be customized to your meet your exact needs.
  • If these first two options do not meet your needs, consider the user defined aggregates that can be built with the Common Language Runtime in SQL Server 2005. These aggregates can be built with VB.NET or C# and then called in your application.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Saturday, October 17, 2009 - 3:37:08 PM - admin Back To Top (4227)

vallibe,

Thank you for the URL with additional information.

Thank you,
The MSSQLTips Team


Friday, October 2, 2009 - 11:55:50 PM - vallibe Back To Top (4120)
hi, aggregate functions r the useful one. they perform calculation on a set of values and return a single value. sql server 2008 has some more useful functions. i found one article which is really helped me. u can also see this on http://www.sql2008.info/sql2008/post/SQL-Server2008-Functions.aspx. if anyone have doubts in sql server 2008 functions, let me know i ll help.














get free sql tips
agree to terms