Recently a student asked me how he could calculate a Moving Median.
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.
Problem You need use SQL to calculate the Median of a result from SQL Server. Background Suppose you need to calculate the
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
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
Use the Approximate Count Distinct function, APPROX_COUNT_DISTINCT, to return an estimate distinct count of values withing rows. It is best suited for
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
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 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
