SQL Aggregate Functions

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.

Aggregate Function Example for SQL

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.

Latest Posts

  • Calculate Moving Median in SQL

    Kris Wenzel

    ·

    Calculate Moving Median in SQL

    Recently a student asked me how he could calculate a Moving Median.  It’s not as easy as you think, as SQL doesn’t provide a built-in MEDIAN() function, nor an easy way calculate the “moving” aspect. In this article we’ll calculate a Moving median using SQL Server.  Let’s use the PizzaDB sample database for example data.…

  • Use SQL to Find the MEDIAN
    ,

    Kris Wenzel

    ·

    Use SQL to Find the MEDIAN

    Problem You need use SQL to calculate the Median of a result from SQL Server. Background Suppose you need to calculate the Median using SQL. You can loosely define the median as the “middle” value of your data set.  If you were calculating the median by hand, you would use the following rules to do…

  • SQL SUM Function
    ,

    Kris Wenzel

    ·

    SQL SUM Function

    The SQL SUM function returns the total value within a table or group. In its simplest use, SUM() totals a column for all results and returns a single value. In this example the query returns the total orders for all SalesOrderDetail records SELECT SUM(LineTotal) AS OrderTotal FROM Sales.SalesOrderDetail You can also filter your results prior to totaling. Here the SUM() is restricted to two…

  • Placeholder Icon
    ,

    Kris Wenzel

    ·

    SQL COUNT Function

    The SQL COUNT function is an aggregate function used to count rows.  Use it alone within a SELECT statement to return a count of all rows within a table, or with a GROUP BY to provide a count of rows within each group.  Use COUNT(*) to count every record in the grouping or COUNT(expression) to count every record where expression’s result isn’t NULL.  Use DISTINCT with…

  • SQL APPROX_COUNT_DISTINCT Function
    ,

    Justin Osborne

    ·

    SQL APPROX_COUNT_DISTINCT Function

    Use the Approximate Count Distinct function, APPROX_COUNT_DISTINCT, to return an estimate distinct count of values withing rows. It is best suited for very large tables where performance matters over precision. The latest 2019 version of SQL Server introduced many functions to the system and enriched the database engine in order to make it work faster…

  • Placeholder Icon
    ,

    Kris Wenzel

    ·

    SQL MIN Function

    The SQL MIN function returns the smallest value within a table or group.  Throughout this section we’ll use the HumanResource.Employee table for our examples:  Using the data above, MIN calculates the smallest SickLeaveHours amount for the entire table: When used with GROUP BY, MIN returns the largest value within a group.  Here is a similar query showing the minimum SickLeaveHours by JobTitle:  Additional SQL MIN Resources  …

  • Placeholder Icon
    ,

    Kris Wenzel

    ·

    SQL MAX Function

    The SQL MAX function returns the largest value within a table or group. In this section, we’ll use the HumanResource.Employee table for our examples:  Using the data above, MAX calculates the largest SickLeaveHours amount for the entire table:  When used with GROUP BY, MAX returns the largest value within a group. You can see this in this query showing the maximum SickLeaveHours by JobTitle:  Additional SQL MAX Resources   To learn…

  • Placeholder Icon
    ,

    Kris Wenzel

    ·

    SQL AVG Function

    The SQL AVG function returns the average value within a table or group. In this section, we’ll use the HumanResource.Employee table for our examples: Using sample data above, AVG calculates the average SickLeaveHours amount for the entire table: When used with GROUP BY, AVG returns the average value within a group. You can see this in this query showing the maximum SickLeaveHours by JobTitle:  Additional SQL AVG Resources   To learn more,…