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.
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.
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:
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.
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.…
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…
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…
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…
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…
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 …
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…
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,…