Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


SQL Server Window Function Syntax


In this part of the tutorial, we’ll look at how window functions work. What is their syntax and what are those “windows” exactly?


Syntax of Window Functions

The backbone of every window function is the OVER clause, which has the following structure:

window function
    [PARTITION BY expression]
    [ORDER BY expression]
    [ROWS or RANGE clause]

Depending on which function is used, some clauses are optional, mandatory or not allowed. Some functions, like PERCENTILE_DISC have even more clauses. We’ll explore all of the options in later parts of this tutorial.

The PARTITION BY clause defines the segment of rows over which a window function will operate. You can think of it that it does a grouping of your rows, like the GROUP BY clause, but without actually grouping them. The ORDER BY clause sorts the rows inside the segment defined by the PARTITION BY clause. If you use the ROWS or RANGE clause (named frame extents), these limit the number of rows within the segment over which the function will be applied.

Let’s illustrate with an example.  The following query sums the current and the previous row together:

WITH [CTE_source] AS
        [Month]            = YEAR([OrderDate]) * 100 + MONTH([OrderDate])
       ,[SalesAmount]      = SUM([SalesAmount])
    FROM [dbo].[FactResellerSales]
    WHERE YEAR([OrderDate]) = 2013 AND MONTH([OrderDate]) <= 6
        YEAR([OrderDate]) * 100 + MONTH([OrderDate])
   ,[WindowFunction] = SUM([SalesAmount])
                            OVER (PARTITION BY [SalesTerritoryKey]
                                  ORDER BY [Month]
                                  ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
FROM    [CTE_source]
ORDER BY [SalesTerritoryKey],[Month];

Let’s analyze the result set:

explaination of window functions

In the windows defined by the PARTITION BY, SQL Server will calculate the sum of the previous row and the current row for each record of the window.

Logical Query Processing Order

A T-SQL query has a logical order in which all of the clauses are processed:

  1. FROM
    • ON (used in joins)
    • OUTER/INNER/FULL/CROSS used in joins and/or APPLY
  2. WHERE
    • [CUBE / ROLLUP]
    • Calculating expressions

Window functions are part of the SELECT or ORDER BY clause, meaning they are calculated in step 5 OR 6 of the process. In other words, rows can already be filtered out by the WHERE clause or the HAVING clause, or grouped by the GROUP BY clause.

Keep in mind that sometimes you have the same function appear twice in the same expression: once when used in conjunction with a GROUP BY (step 3) and once as a window function (step 5). For example, in the following query we calculate the grand total of the sales amount per year:

     [Year]         = YEAR([OrderDate])
    ,[Sales Amount] = SUM([SalesAmount])
    ,GrandTotal     = SUM(SUM([SalesAmount])) OVER()
FROM [dbo].[FactResellerSales]
GROUP BY YEAR([OrderDate]);
Additional Information

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.


Learn more about SQL Server tools