Window functions are T-SQL functions used to perform a calculation over a set of rows. A window function is similar to an aggregate function. The difference is that window functions do not group the results into a single output row.

Most people know how to use aggregate functions to calculate aggregate values, such as sum, average, maximum, minimum, and count. The results of these aggregate functions are grouped by specific data using the GROUP BY clause.

Then how if we want to display the detail data and aggregated data side-by-side? For example, to display the EmployeeID, Gender, Age, and AVG(Age) by Gender in one row. It’s possible doing this using sub-query, but we can do that in a single query using window functions.

Let’s take a look at the following example to see the difference between aggregate function vs. window function.

Table: Students

We use the following table for our example. Let’s calculate the average GPA of each Major using aggregate function and a window function.

ID Fullname Age Major GPA
1 Lala Maulida 20 Graphic Design 3.40
2 Dini Wulandari 19 Computer Science 3.20
3 Novi Maryam 22 Computer Science 2.94
4 Irfan Zulkarnaen 21 Electrical Engineering 2.47
5 Hilda Zaenab Kuswandari 19 Computer Science 3.67
6 Gunawan Raden Pradipta 20 Electrical Engineering 3.10
7 Yessi Bauti Farida 22 Computer Science 3.86
8 Umar Hakim 24 Electrical Engineering 2.20
9 Elvina Handayani 20 Graphic Design 3.62
10 Rani Purwanti 25 Computer Science 3.51
11 Harjasa Wibowo 22 Electrical Engineering 3.20
12 Agnes Sabrina Pertiwi 21 Graphic Design 2.82
13 Luwes Kuswoyo 20 Electrical Engineering 2.80
14 Bagio Semar Prasetyo 18 Computer Science 2.68
15 Koko Hidayah 19 Electrical Engineering 2.95

Script to create the table and insert data

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
CREATE TABLE Students (
	ID int IDENTITY(1,1) NOT NULL,
	Fullname nvarchar(50) NULL,
	Age int NOT NULL,
	Major nvarchar(50) NULL,
	GPA decimal(3, 2) NULL,
	CONSTRAINT PK_Students PRIMARY KEY(ID)
)
GO

INSERT INTO Students (Fullname, Age, Major, GPA) 
	VALUES('Lala Maulida', 20, 'Graphic Design', 3.40)
INSERT INTO Students (Fullname, Age, Major, GPA) 
	VALUES('Dini Wulandari', 19, 'Computer Science', 3.20)
INSERT INTO Students (Fullname, Age, Major, GPA) 
	VALUES('Novi Maryam', 22, 'Computer Science', 2.94)
INSERT INTO Students (Fullname, Age, Major, GPA) 
	VALUES('Irfan Zulkarnaen', 21, 'Electrical Engineering', 2.47)
INSERT INTO Students (Fullname, Age, Major, GPA) 
	VALUES('Hilda Zaenab Kuswandari', 19, 'Computer Science', 3.67)

INSERT INTO Students (Fullname, Age, Major, GPA) 
	VALUES('Gunawan Raden Pradipta', 20, 'Electrical Engineering', 3.10)
INSERT INTO Students (Fullname, Age, Major, GPA) 
	VALUES('Yessi Bauti Farida', 22, 'Computer Science', 3.86)
INSERT INTO Students (Fullname, Age, Major, GPA) 
	VALUES('Umar Hakim', 24, 'Electrical Engineering', 2.20)
INSERT INTO Students (Fullname, Age, Major, GPA) 
	VALUES('Elvina Handayani', 20, 'Graphic Design', 3.62)
INSERT INTO Students (Fullname, Age, Major, GPA) 
	VALUES('Rani Purwanti', 25, 'Computer Science', 3.51)

INSERT INTO Students (Fullname, Age, Major, GPA) 
	VALUES('Harjasa Wibowo', 22, 'Electrical Engineering', 3.20)
INSERT INTO Students (Fullname, Age, Major, GPA) 
	VALUES('Agnes Sabrina Pertiwi', 21, 'Graphic Design', 2.82)
INSERT INTO Students (Fullname, Age, Major, GPA) 
	VALUES('Luwes Kuswoyo', 20, 'Electrical Engineering', 2.80)
INSERT INTO Students (Fullname, Age, Major, GPA) 
	VALUES('Bagio Semar Prasetyo', 18, 'Computer Science', 2.68)
INSERT INTO Students (Fullname, Age, Major, GPA) 
	VALUES('Koko Hidayah', 19, 'Electrical Engineering', 2.95)

Basic aggregate function

Calculate average GPA by Major.

SELECT	Major, AVG(GPA) AvgGpa
FROM	Students
GROUP BY Major

Result:

Major AvgGpa
Computer Science 3.310000
Electrical Engineering 2.786666
Graphic Design 3.280000

As we can see in the above result, the average values are grouped by the three rows of major.

Window function

The window function is used if we want to show the aggregate value without grouping.

For example, we want to compare the students’ GPA with the average GPA of their Major side by side. The columns we want to display are the students’ names, major, GPA, and the average GPA of each Major.

We can solve the above problem by using a window function as follows:

1
2
3
4
5
SELECT	Fullname
		, Major
		, GPA
		, AVG(GPA) OVER(PARTITION BY Major) AvgGpa 
FROM	Students

Result:

The average GPA of each Major is displayed in each row without grouping.

Using the window function to calculate the average GPA