Database developers usually know about aggregate functions, but very few know usage of Window functions in SQL Server, these operate on a set of rows and return a single aggregated value for each row, so in this article, I have mentioned what is window function in sql server and how to use window function in SQL Server with example.
In Window function, term "Window" describes the set of rows in the database on which the function will operate. Window Functions were introduced in SQL Server 2005 but with SQL Server 2012, we can enjoy full range of Window Functions.
Since SQL Server 2005 we support for some window functions, they are: ROW_NUMBER, RANK, DENSE_RANK and NTILE.
But from 2012 or above SQL Server, we have 3 type of Windows Functions:
- Aggregate Window Functions: SUM(), MAX(), MIN(), AVG(). COUNT()
- Ranking Window Functions: RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()
- Value Window Functions: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()
Window functions all use the
OVER() clause, which is used to define how the function is evaluated. The OVER() clause accepts three different arguments:
- PARTITION BY: Resets its counter every time the stated column(s) changes values.
- ORDER BY: Orders the rows the function will evaluate. This does not order the entire result set, only the way the function proceeds through the rows.
- ROWS BETWEEN: Specifies how to further limit the rows evaluated by the function.
We will use Order By as arguments in our examples.
So basic syntax of Window function in SQL Server is as below
window_function ( [ ALL ] expression ) OVER ( [ PARTITION BY partitionList ] [ ORDER BY orderList] )
So let's create a sample database table with sample values
CREATE TABLE [dbo].[Order] ( Id INT, OrderDate DATE, CustomerName VARCHAR(250), City VARCHAR(100), OrderAmount MONEY ) INSERT INTO [dbo].[Order] SELECT '1001','04/01/2022','John Wick','New York',10000 UNION ALL SELECT '1002','04/02/2022','David Yates','Chicago',20000 UNION ALL SELECT '1003','04/03/2022','John Wick','New York',5000 UNION ALL SELECT '1004','04/04/2022','Michael Smith','Chicago',15000 UNION ALL SELECT '1005','04/05/2022','David Warner','Chicago',7000 UNION ALL SELECT '1006','04/06/2022','Paul Smith','Chicago',25000 UNION ALL SELECT '1007','04/10/2022','Andrew Smith','Arlington',15000 UNION ALL SELECT '1008','04/11/2022','David Brown','Arlington',2000 UNION ALL SELECT '1009','04/20/2022','Rupert Smith','New York',1000 UNION ALL SELECT '1010','04/25/2022','Palmer Smith','Chicago',500
Now suppose we want to get sum of each city, then using Aggreate function SUM() we can get it.
SELECT City, SUM(OrderAmount) total_order_amount FROM [dbo].[Order] GROUP BY City
This will return output as below
City total_order_amount ----------------------------------- Arlington 17000.00 Chicago 67500.00 New York 16000.00
But as you can see from above output, rows returned are grouped value of each city.
This does not happen with window aggregate functions. Rows retain their identity and also show an aggregated value for each row.
So if we use window functions for above example query, it would be as below
SELECT Id, OrderDate, CustomerName, City, OrderAmount ,SUM(OrderAmount) OVER(PARTITION BY City) as GrandTotal FROM [dbo].[Order]
and the output of the above query will be as below
As you can see from output, query now, aggregates the data for each city and shows the sum of total order amount for each of them.
Similary, we can use it with Rank() function.
Rank() function is used to give rank to each row based on the specified value and if the value of 2 rows are same, we give it same rank.
So, if we use Rank() by order Amount with Window function, query would be as below
SELECT Id,OrderDate,CustomerName,City, RANK() OVER(ORDER BY OrderAmount DESC) [Rank] FROM [dbo].[Order]
and output of the above query will be:
Similarly, you can use it in various other aggregate functions and make quering data more useful.
You may also like to read: