Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

SQL Server TSQL Aggregate Functions


By:   |   Read Comments (2)   |   Related Tips: More > 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.


Last Update:






About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



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

vallibe,

Thank you for the URL with additional information.

Thank you,
The MSSQLTips Team


Friday, October 02, 2009 - 11:55:50 PM - vallibe Back To Top
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.

Learn more about SQL Server tools