We usually 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
GROUP BY clause.
A window function is similar to an aggregate function. The difference is that in window functions, the results are not grouped into a single output row.
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 case. Let’s calculate the average GPA of each Major using aggregate function and a window function.
Note: you can find the script to create the table and insert the data at the bottom of this post.
Basic aggregate function
Calculate average GPA by Major.
SELECT Major, AVG(GPA) AvgGpa FROM Students GROUP BY Major
As you 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’ name, major, GPA, and the average GPA of each Major.
We can solve the above problem by using a window function as below.
The average GPA of each Major is displayed in each row without grouping.
Script to create table and insert data