SQL Server Aggregate Functions with OVER Clause

Did you know that you can use the SQL Server aggregate functions SUM, COUNT, MAX, MIN and AVG with an OVER Clause now?

Using an OVER clause you can produce individual record values along with aggregate values to different levels, without using a GROUP BY clause. You can also produce running totals, rolling averages, etc.

The examples in this article are based on the AdventureWorks2014 database. 

Aggregate To Different Levels with OVER (PARTITION BY ….)

The following example shows individual order records along with the grand total of all sales, the annual revenue for the year of the order and the total customer revenue for the customer that placed the order.

Here is the code for the above query:

SELECT YEAR(OrderDate), SalesOrderID, CustomerID, TotalDue,
		SUM(TotalDue) OVER() AS 'Total Business Sales', 
		SUM(TotalDue) OVER (PARTITION BY YEAR(OrderDate)) AS 'Total Annual Sales',
		SUM(TotalDue) OVER (PARTITION BY CustomerID) AS 'Total Customer Sales'
FROM Sales.SalesOrderHeader
ORDER BY CustomerID, YEAR(OrderDate)

SUM(TotalDue) OVER() AS 'Total Business Sales'

This expression produces a grand total across the whole data set. There is no partitioning of the data. This is why every record shows the same value for the “Total Business Sales” column.

SUM(TotalDue) OVER (PARTITION BY YEAR(OrderDate)) AS 'Total Annual Sales'

This expression instructs SQL Server to group (partition) the data by the YEAR of the orderdate and produce an annual sales total. You will see that this value is the same for each common year.

SUM(TotalDue) OVER (PARTITION BY CustomerID) AS 'Total Customer Sales'

This expression instructs SQL Server to group (partition) the data by the CustomerID and produce a customer sales total. You will see that this value is identical where the CustomerID for an order is the same.

The OVER clause can be used with all of the aggregate functions.

The following example displays individual SalesOrderDetail records along with the total quantity ordered for the order, the average order quantity for the order, the number of items on the order, the lowest order quantity on the order and the highest quantity on the order.

Here is the code for the above example:

SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total Quantity Ordered'
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Average Quantity Ordered'
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Number Of Items On Order'
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Lowest Quantity Ordered'
    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Highest Quantity Ordered'
FROM Sales.SalesOrderDetail 

Running Totals With The ORDER BY Sub Clause

The ORDER BY sub clause enables a running total to be generated.

The following example shows the monthly revenue for each month, along with the running total for the year.

Here is the code for the above example:

SELECT DISTINCT YEAR(OrderDate) AS OrderYear, MONTH(OrderDate) AS OrderMonth,
	   SUM(TotalDue) 
			OVER(PARTITION BY YEAR(OrderDate),MONTH(OrderDate) 
			     ORDER BY YEAR(OrderDate),MONTH(OrderDate)) AS "Monthly Revenue", 
		   SUM(TotalDue) 
			OVER(PARTITION BY YEAR(OrderDate) 
			     ORDER BY MONTH(OrderDate)) AS "Running Revenue Total"
FROM Sales.SalesOrderHeader
ORDER BY YEAR(OrderDate), MONTH(OrderDate)

We see that the totals for each month are different and the fourth column shows the total growing through the months of the year, but the running total starts again when a new year starts.

If we look at December 2011, the value 14155699.525 is the total of all 12 months for 2011.

If we look at June 2011, the value 1074117.4188 is the total for May and June 2011.

SUM(TotalDue)
            OVER(PARTITION BY YEAR(OrderDate)
                       ORDER BY MONTH(OrderDate)
                      ) AS "Running Revenue Total"

The OVER clause for the fourth column, “Running Revenue Total” has both a PARTITION and ORDER BY sub clause.

• The PARTITION BY defines the group to be the YEAR
• The ORDER BY defines that we evaluate the annual total after each month

Without the ORDER BY Clause the annual total is evaluated after each year (the partition determines the order). We can see this in the following example:

Here is the code for the above example:

SELECT DISTINCT YEAR(OrderDate) AS OrderYear, MONTH(OrderDate) AS OrderMonth,
	   SUM(TotalDue) 
			OVER(PARTITION BY YEAR(OrderDate),MONTH(OrderDate) 
			     --ORDER BY YEAR(OrderDate),MONTH(OrderDate)
				 ) AS "Monthly Revenue", 
		   SUM(TotalDue) 
			OVER(PARTITION BY YEAR(OrderDate) 
			     --ORDER BY MONTH(OrderDate)
				 ) AS "Running Revenue Total"
FROM Sales.SalesOrderHeader
ORDER BY YEAR(OrderDate), MONTH(OrderDate)

If you are itching to learn more why not book on to our SQL Server Database Querying training courses? This link will take you to the course outlines:

http://ptr.co.uk/databases-business-intelligence-courses. We have a full portfolio of SQL Server Training Courses available.