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.