By: Jeremy Kadlec | Updated: 2007-04-12 | 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.
About the author
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
Article Last Updated: 2007-04-12
