SQL Aggregate functions like SQL MAX, SQL MIN, SQL COUNT And SQL AVERAGE function
SQL Aggregate functions are necessary to summarize numerical data in a SQL Server database. We have already looked at other transact SQL statements like SELECT, INSERT, DELETE and UPDATE SQL statements. Those typically are used to return rows of data that matches the criteria in the WHERE clause. SQL statements using aggregate functions like MIN or MAX however return just one value after SQL server has already applied the aggregation and summarized the data. In this manner SQL Server aggregate functions perform their operation across multiple rows of data.
For today’s discussion we are going to look at AdventureWorks2008 sample database in SQL Server 2008. You can download the associated data from this location:
Products:
http://www.sqlserver2008tutorial.com/blog/sql-aggregate-functions/production_products.xls
Orders:
http://www.sqlserver2008tutorial.com/blog/sql-aggregate-functions/sales_order_header.xls
There are a number of SQL aggregate functions; however we are only going to look at the important ones so here we go:
SQL MIN Aggregate function:
The SQL function is used to return the minimum value in the select list, ignoring any Null value. This would typically be used let’s say in a PRODUCTS table to find your cheapest product.
SELECT
MIN(LISTPRICE) AS [MINIMUM PRICED ITEM]
FROM PRODUCTION.PRODUCT
WHERE SIZE IS NOT NULL
--8.99
SQL MAX Aggregate function:
This Aggregate function is used to return the maximum value in the select list, ignoring th any Nulls. Similar to the SQL MIN function, the SQL Max function can be used to find the highest priced item in the above products table. Here is the SQL syntax for MIN function:
SELECT
MAX(LISTPRICE) AS [MAXIMUM PRICED ITEM]
FROM PRODUCTION.PRODUCT
WHERE SIZE IS NOT NULL
--3578.27
SQL SUM Aggregate function:
This SQL function returns the total sum of all items in the select list, ignoring any Nulls. The sum function will add up all the values for a column in a certain table. For example we are going to use the SALESORDERHEADER table and summarize the TOTALDUE field
SELECT SUM(TOTALDUE) AS SUMMATION
FROM SALES.SALESORDERHEADER
WHERE CUSTOMERID='29898'
--197634.248
SQL AVG Aggregate function:
SQL provides the AVG function to return the average of values in the select list, ignoring any Nulls. This is an important function which will help you find average value of let’s say product prices in your product table. We are going to use the same SALESORDERHEADER table from the prior example to show you SQL average function in action
SELECT AVG(TOTALDUE) AS AVERAGE
FROM SALES.SALESORDERHEADER
WHERE CUSTOMERID='29898'
--49408.562
SQL COUNT Aggregate function:
A lot of times all you’re trying to figure out his how many rows or records apply to a certain condition. This is where the sql COUNT function comes in handy as it will simply count the number of rows that meet your criteria. The COUNT aggregate function returns the number of items in the select list. This ignores the NULL values unless you use COUNT(*). This is by far the most important aggregate function that you will be using. Here is an example from the SALESORDERHEADER table.
SELECT COUNT(TOTALDUE) AS [ITEM COUNT]
FROM SALES.SALESORDERHEADER
WHERE CUSTOMERID='29898'
--4 RECORDS
Sample video on Aggregate Functions here:
http://www.learningsqlserver2008.com/products.htm
