Use aggregate functions in SQL to calculate result based on a group of rows.  You define the group using GROUP BY, but you can also use aggregate values with widow function partitions, as well as over the entire result set.

SQL Example Using Aggregate Functions

Two commonly used SQL aggregate functions are SUM and COUNT.  In this example

SELECT TerritoryID, SUM(Freight) TotalFreight, Count(SalesOrderID) NumberItems
FROM Sales.SalesOrderHeader
GROUP BY TerritoryID
ORDER BY TerritoryID

Here we get the Sum of Freight costs by Territory for all SalesOrders.  While doing so the database also counts SalesOrders per Territory.

When working with Aggregate SQL Functions with a column, keep in mind that you’ll want to the function; otherwise, the database automatically assigning meaningless columns names such as column1.

SQL Aggregate Functions

Keep in mind when use these functions, you can use them across the entire result.  For example, to get a count of rows in a table, or within the group using BROUP BY.

Here are some commonly used functions:

• COUNT –  The COUNT() function returns the number of rows within a group.
• MIN – Use the MIN() function to return the smallest value found within a group.
• MAX – Use the MAX() function to return the largest value found within a group.
• SUM – The SUM() function returns a totaled value within a group.
• COUNT_APPROX – The COUNT_APPROX() aggregate function returns an approximate row count with a group.  It is handy for extremely large tables.

Working Around Limitations

A question my may ask is whether you can next aggregate functions such as

SELECT TerritoryID, AVG(Sum(Freight)) AverageFreight
FROM Sales.SalesOrderHeader
GROUP BY TerritoryID
ORDER BY TerritoryID

To calculate an average of each Territories totals.  The database doesn’t allow this.  But you can do a work around, using derived tables to make it happen.  Here is what it looks like:

SELECT AVG(TotalFreight) AvgTerritoryTotalFreight
FROM (
    SELECT TerritoryID, SUM(Freight) TotalFreight
    FROM Sales.SalesOrderHeader
    GROUP BY TerritoryID
) d

I talk about this more within my Derived Tables article.