SQL Server Window Functions
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.
We use the following table for our example. Let’s calculate the average GPA of each Major using aggregate function and a window function.
|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
Basic aggregate function
Calculate average GPA by Major.
SELECT Major, AVG(GPA) AvgGpa FROM Students GROUP BY Major
As we can see in the above result, the average values are grouped by the three rows of major.
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:
The average GPA of each Major is displayed in each row without grouping.