Window Functions VS Aggregate Functions in MySQL

While learning MySQL, you might get stuck multiple times thinking about how this is different from that, right? The same goes for the window functions and the aggregate functions. If you have recently started learning MySQL, then you might be thinking about how window functions differ from aggregate functions.

In this tutorial, we will see how these two are different, what are similarities in them, and when to choose which function. We will start with the basics of both functions and then quickly see the differences. So without further ado, let’s get started!

Also read: Difference Between GROUP BY and ORDER BY – Simple Explanation

Difference Between Aggregate Functions vs. Window Functions

We have seen both functions with examples. We can now find the similarities and differences between them.

Similarities

Both, aggregate functions and window functions-

• Works on a group of rows/ set of rows
• Can be used to group one or more columns
• Can be used to find the aggregate values

Differences

Aggregate functions and window functions differ by-

• Window functions use OVER() clause instead of GROUP BY() clause
• Aggregate functions can be used as window functions but not vice-versa
• Rows do not collapse in window functions.

Here, is a quick summarization of the similarities and differences in the following table

What are MySQL Aggregate Functions?

The functions which take input as a multiple/set of records and return a single scaler value are aggregate functions. Following are some of the widely used aggregate functions which are provided by MySQL.

• AVG() – returns the average value of the given records.
• COUNT() – returns the count of the rows from the result set.
• MIN(), MAX() – returns the minimum and maximum values from the given arguments.
• SUM() – returns the total sum of the arguments.

These are not only the aggregate functions that are available in MySQL. Here is the complete list of all aggregate functions with their description that you can check out real quick.

Note that, aggregate functions are used with the GROUP BY clause to calculate the aggregate value. It doesn’t make any sense to use aggregate functions without the GROUP BY clause and vice-versa.

Let’s take an example to understand aggregate functions.

Below are two table descriptions consisting of some data.

Now, let’s find the minimum marks, maximum marks, and average marks from the given data.

-- minimum marks
SELECT MIN(m.maths), MIN(m.english),MIN(m.science)
FROM students s
LEFT JOIN student_marks m
ON s.roll_number=m.roll_number;

-- maximum marks
SELECT MAX(m.maths), MAX(m.english),MAX(m.science)
FROM students s
LEFT JOIN student_marks m
ON s.roll_number=m.roll_number;
Code language: SQL (Structured Query Language) (sql)

As you can see, we have got the correct result. However, the aggregate functions are often used with the GROUP BY clause to write complex queries.

What are MySQL Window Functions?

Window functions in SQL work on a collection of rows known as a window frame. For each row from the underlying query, they give back a single value.

To define a window, we use the OVER() clause which can also be used to define a specific column name, similar to the GROUP BY clause.

There is an array of window functions available in MySQL. You can check out the tutorials on different window functions from here.

Note that, window functions include aggregate functions as well as non-aggregate functions. However, the OVER() clause is mandatory to be used with the non-aggregate functions. You can check the list of all aggregate functions on the MySQL dev blog.

Let’s take an example of the window function.

For this, we will consider the following table schema.

Here you can see, there are multiple students present in the table from the same class. So, we can make a window of the class column.

We will use the RANK() function to assign a ranking to the students from all classes. You can read the detailed guide on the RANK() function here.

Note that, if two or more students have the same marks, then they will get the same ranking. However, the next individual or a group of students having the same marks will not get the immediate ranking to the previous rank.

SELECT id, name, class, marks, 
RANK() OVER(ORDER BY marks DESC) as studs_rank
FROM marks;Code language: SQL (Structured Query Language) (sql)

As you can see, rankings are distributed to all students.

Conclusion

In this tutorial, we have learned about the similarities and differences between aggregate functions and window functions. I highly recommend learning the CTE (common table expression) to learn to make use of the aggregate and window functions. s