solving sql server problems for millions of dbas and developers since 2006



SQL Server DBA Tips SQL Server Developer Tips SQL Server Business Intelligence Tips SQL Server Career Tips SQL Server Tip Categories SQL Server Tutorials SQL Server Webcasts SQL Server Whitepapers SQL Server Tools SQL Server Questions and Answers MSSQLTips Authors About MSSQLTips SQL Server User Groups MSSLQTips Giveaways MSSQLTips Advertising Options

MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Twitter Page MSSQLTips Google+ Page





SQL Product Highlight

Idera - SQL safe backup

Idera's SQL safe provides a high-performance backup and recovery solution for Microsoft SQL Server. SQL safe saves money by reducing database backup time by up to 50% over native backups and reducing backup disk space requirements by up to 95%. SQL safe also enables complete "hands-free" automated backup of your entire SQL Server infrastructure and ensures compliance with your organization's backup and recovery policies. From implementations with tens of SQL servers to enterprises with hundreds of servers spread around the globe, SQL safe is the only SQL Server backup and recovery solution that scales to meet the challenge.

Learn more!




SQL Server TSQL Aggregate Functions

By: | Read Comments (2) | Print

Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009.



Related Tips: More

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.


Related Tips: More | Become a paid author


Last Update: 4/12/2007

Share: Share 



Connect with MSSQLTips.com


Follow us on twitter...


Let's connect on facebook!


Join us on Google+



Comments and Feedback:

Friday, October 02, 2009 - 11:55:50 PM - vallibe Read The Tip
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.

Saturday, October 17, 2009 - 3:37:08 PM - admin Read The Tip

vallibe,

Thank you for the URL with additional information.

Thank you,
The MSSQLTips Team


Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email   Notify for updates
Comments
*Enter Code refresh code


 
Sponsor Information
Try the free performance monitoring tool from Idera!

Time-strapped database professionals use SQL Monitor to look after their servers. Try it online.

SQL Servers keeping you up at night? Contact the Edgewood SQL Server consultants for a Health Check.

Find and Fix SQL issues with Foglight Performance Analysis. Get a free copy.

Join the over million SQL Server Professionals who get their issues resolved daily.

Free web casts for DBAs and Developers on Performance Tuning, Development, Administration and more....

Copyright (c) 2006-2012 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups
Some names and products listed are the registered trademarks of their respective owners.

Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com