MySQL Window Functions Vs GROUP BY

The new learners or even some intermediate SQL users think the window functions and the aggregate functions or a GROUP BY clause are the same, but it’s not. Even if they look very similar, there is a small difference between them. In this tutorial, we will see the difference between the window functions and the GROUP BY clause. So, let’s get started!

The window functions work on a set of records or a group of records to find the result. The GROUP BY clause also works on the group of records. The similarity between the window functions and the GROUP BY clause is that both can work with aggregate functions such as AVG, MIN, MAX, etc on a group of records.

Let’s see the detailed information on the window functions as well as the GROUP BY clause with some examples. Then we will see similarities and compare them as well.

Window Functions in MySQL

The window functions work on the group of records where we can create the window of the records. It is also known as the sliding window.

Window functions allow us to use aggregate functions such as AVG, MIN, MAX, COUNT etc as well as non-aggregate functions such as RANK, DENSE_RANK, LAG, LEAD, etc. The reason why window functions are different is that they don’t collapse the rows in the result set. Window functions return the result for individual rows, which means it keeps the original table data as well.

For example-

Let’s say we have the following table of student marks.

Now, we want to assign the ranks to students based on their marks. However, if two or more students have the same marks, they all should get the same rank. Also, the output must consist of the student names as well as marks with the ranks.

SELECT id, name, class, marks,
DENSE_RANK() OVER(ORDER BY marks) as 'rank'
FROM marks;Code language: SQL (Structured Query Language) (sql)

Here, we have used the DENSE_RANK function to assign the ranks to students. Note that, no rows are collapsed here and individual records are preserved.

We have not created any window/partition here because we want the whole data to work as a single partition. Also, the ORDER BY sub-clause is necessary to assign the ranks.

Note that, we have assigned the ranks in ascending order. That means the lowest mark will get the top spot.

As you can see, we have got the student data with the correct ranks assigned to them. This is the power of the window functions. To read and learn more about window functions, do check out our detailed articles on window functions.

GROUP BY Clause in MySQL

The GROUP BY clause is used with aggregate functions such as MAX, MIN, AVG, COUNT etc. It works on the set of records to find a scalar value for each group. Unlike the window functions where individual records are preserved and the new value is appended as a new column for each record, the GROUP BY clause collapses the group of rows and finds one value for each group.

Note that, the window functions come under the category of “functions” whereas the GROUP BY is a clause which is used to organize the group of rows and find the result.

Let’s take an example.

Consider the above table in the window functions example. We have to find the details of the students having minimum marks from each division.

SELECT * FROM marks 
WHERE marks IN
(SELECT MIN(marks) AS marks 
FROM marks GROUP BY class)
ORDER BY class;Code language: SQL (Structured Query Language) (sql)

Here, we have used the inner query to find the minimum marks from each division using the GROUP BY clause. Note that, the inner query gives only five records, which means it collapses the group of rows based on divisions to find the minimum marks. Then we use the outer query to find the details of the student.

As you can see, the E division has two students having the same marks, therefore the result contains both names. This is because of the outer query and not because of the GROUP BY clause.

To read more about the GROUP BY clause, you can refer to our in-detail guide.

Wrapping Up

In this tutorial, we have seen detailed information about the window functions as well as the GROUP BY clause. Let’s recap quickly what we have learned. Windows functions make the grouping of the rows to find the result without collapsing any of the rows. Whereas the GROUP BY clause also forms the groups of the rows according to a certain condition. However, it collapses the rows to find the scalar value for each group, unlike the window functions. I hope everything is clear to you and don’t forget to share with those who are still confused about window functions and the GROUP BY clause.