Home MS Office Complete Guide Sql Server Aggregate Functions

Complete Guide Sql Server Aggregate Functions


SQL Server Aggregate Functions help to perform analysis across rows, such as counting rows meeting specific criteria or summarizing total sales for all orders.  SQL Server Aggregate functions return a single value and can be used in SELECT statements with a single expression, such as SELECT, HAVING, and ORDER BY clauses.


Aggregate functions ignore NULLs, except when using COUNT(*).  SQL Server Aggregate functions in a SELECT list do not generate a column alias. You may wish to use the AS clause to provide one. Aggregate functions in a SELECT clause operate on all rows passed to the SELECT phase. If there is no GROUP BY clause, all rows will be summarized. SQL Server provides many built-in aggregate functions.  The following table lists the commonly used functions:

SQL Server Aggregate Functions

Sql Server Aggregate Functions

The following code snippet demonstrates the use of built-in aggregate in a SELECT clause:

SELECT AVG([UnitPrice]) AS AvgUnitPrice,

MIN([OrderQty])AS MinQty,

MAX([UnitPriceDiscount]) AS MaxDiscount

FROM Sales.SalesOrderDetail;

Since the query does not use a GROUP BY clause, all rows in the table will be summarized by the aggregate formulas in the SELECT clause.The output is shown in the following figure:

SQL Server Aggregate Functions

When using aggregates in a SELECT clause, all columns referenced in the SELECT list must be used as inputs for an aggregate function or must be referenced in a GROUP BY clause.  Failing this, there will be an error. The following code snippet will return an error:

SELECT SalesOrderID, AVG(UnitPrice) AS AvgPrice

FROM Sales.SalesOrderDetail;

This returns an error stating that the column Sales.SalesOrderDetail.SalesOrderID is invalid in the SELECT list for it is not contained in any an aggregate function or the GROUP BY clause.

As the query is not using a GROUP BY clause, all rows will be treated as a single group. All columns, therefore, must be used as inputs to SQL Server aggregate functions.

To correct or prevent the error, one needs to remove SalesOrderID from the query. Besides using numeric data, aggregate expressions can also include date, time, and character data for summarizing. Following code snippet returns the earliest and latest order date, using MIN and MAX function:

SELECT MIN(OrderDate)AS Earliest,

MAX(OrderDate) AS Latest

FROM Sales.SalesOrderHeader;

The following figure shows the output:

SQL Server Aggregate Functions

Thanks for read  complete article, I hope you have understood as well and you will learn it something new. Kindly, share my content on social networks and subscribe my website social networks pages like Facebook, Google+, Twitter, YouTube and Email for post updates. If you have any question / suggestion about this topic comment or email me after received your email or comment I will reply with your answer maximum 24 hours. Thanks Again for a visit, Read my Complete Article and Watch my Videos. Keep touch my website I will share popular programming Languages like Java Fundamental and C# very soon.