mssqltips logo

SQL Server Window Functions Examples



By:
Overview

Let’s start the tutorial with a couple of T-SQL queries where we use window functions. We’ll use those examples to illustrate the potential power of the functions and how you can use them in your daily programming.

Throughout this tutorial, the AdventureWorks2017 data warehouse is used as a sample database. You can download the database from Github.

If the examples seem a bit complicated, don’t worry, we’ll explain every one of them in this tutorial.

Example 1 – Calculate Grand Total in SQL Server

Let’s start easy with calculating the grand total of all sales. When using window functions, this grand total will be displayed on each row:

SELECT DISTINCT
     SalesYear          = YEAR([OrderDate])
    ,SalesGrandTotal    = SUM([SalesAmount]) OVER()
FROM [AdventureWorksDW2017].[dbo].[FactInternetSales]
ORDER BY SalesYear;

This query returns the following results:

grand total with sum

The query raises a couple of questions:

  • Why did we use DISTINCT?
  • Why is the result on each row the same?
  • What is OVER()?

The OVER() clause is what makes an ordinary function like SUM a window function. As we’ll see in this tutorial, there are some functions that can only be used as a window function, but other aggregate functions can be used in other scenarios as well. Functions like SUM, AVG, MIN and MAX were being used with the GROUP BY clause years before window functions were introduced.

A window function – SUM in this example – returns a result for each row. Since we calculate the grand total, it’s the same for each row. If we didn’t use the DISTINCT clause, a row would have been returned for every record in the FactInternetSales table.

too many rows

By using DISTINCT, we limit the result set to only the 5 distinct values for the order year.

Calculating the grand total like we did in the query doesn’t seem useful at first, but the result can be used for example to calculate a percentage of total. We’ll look into that use case in the next part of this tutorial.

Example 2 – Find Top 5 Records in SQL Server

In the next example we’re going to use a typical window function: RANK. This function allows us to find customers who have generated the most sales.

SELECT
     [CustomerAlternateKey]
    ,[SalesAmount]
    ,CustomerRank = RANK() OVER (ORDER BY [SalesAmount] DESC)
FROM
( -- first calculate the sales amount for each customer
    SELECT
         c.[CustomerAlternateKey]
        ,[SalesAmount] = SUM(f.[SalesAmount])
    FROM [dbo].[FactInternetSales]  f
    JOIN [dbo].[DimCustomer]        c ON [c].[CustomerKey] = [f].[CustomerKey]
    GROUP BY [c].[CustomerAlternateKey]
) tmp;

This returns the following result:

example of RANK

Since we ordered by descending sales amount in the OVER() clause, the customers with the highest sales amount have are ranked first.

Example 3 – Calculate the Median Value in SQL Server

Let’s calculate the median salary for the AdventureWorks employee per department. We’ll use the PERCENTILE_CONT function. The 0.5 percentile is the same as the median value.

SELECT DISTINCT
     [DepartmentName]
    ,MedianRate = PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY [BaseRate]) OVER (PARTITION BY [DepartmentName])
FROM [dbo].[DimEmployee]
ORDER BY MedianRate DESC;

The result set:

calculating the median value

The PERCENTILE_CONT function has a more complex syntax than other window functions. We’ll get to the bottom of this in part 8.

Additional Information

Here are some related tips:


Last Update: 5/31/2018




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools