Window functions are used in SQL Server to perform data-analysis calculations. A data-analysis calculation is one you apply to a set of rows that returns a single value. Aggregate calculations fall under this category. In contrast to group functions which are applied to groups of rows defined by a grouped query, window functions are applied to windows of rows defined by a windowed query.

Window functions fall under four categories:

1. Aggregate
2. Ranking
3. Offset
4. Statistical

Standard SQL provides extensive coverage of window functions SQL Server’s T-SQL implements a subset of the standard. Window functions are fairly new to SQL Server. Some of them were introduced in SQL Server 2012.

Simple Example – Aggregate

There is a post here at this site on aggregate window functions, but here I will start with a very simple example. In a non-production server you can create the table with the following script that was generated by SSMS by right-clicking the database name and choosing Tasks and then generate scripts (and so on).

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Window](
	[Id] [int] NOT NULL,
	[Type] [varchar](3) NULL,
	[SubType] [varchar](3) NULL,
	[Amount] [int] NULL,
 CONSTRAINT [PK_Window] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Window] ([Id], [Type], [SubType], [Amount]) VALUES (1, N'A', N'01', 7)
INSERT [dbo].[Window] ([Id], [Type], [SubType], [Amount]) VALUES (2, N'A', N'02', 2)
INSERT [dbo].[Window] ([Id], [Type], [SubType], [Amount]) VALUES (3, N'B', N'01', 3)
INSERT [dbo].[Window] ([Id], [Type], [SubType], [Amount]) VALUES (4, N'A', N'01', 5)
INSERT [dbo].[Window] ([Id], [Type], [SubType], [Amount]) VALUES (5, N'B', N'01', 1)
GO

Here below are two window functions and a GROUP BY clause.

SELECT  [Id],[Type],[SubType],[Amount],
	  SUM(Amount) OVER() as Total_amt
  FROM dbo.Window

SELECT  [Id],[Type],[SubType],[Amount],
	  SUM(Amount) OVER(PARTITION BY [Type]) as Total_Type_amt
  FROM dbo.Window

  SELECT [Type], SUM(Amount) as Total_Type_amt
  FROM dbo.Window
  GROUP BY [Type]

Below is the screenshot of the results in SSMS.

We are not limited by SUM. Here below I will use average.

SELECT  [Id],[Type],[SubType],[Amount],
	  AVG(Amount) OVER(PARTITION BY [Type]) as Mean_Type_amt
      FROM dbo.Window