Window Functions VS Aggregate Functions in MySQL

Aggregate Functions Vs Window 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

Aggregate Functions & 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 aggregate values such as AVG, SUM, COUNT
Aggregate FunctionsWindow Functions
Uses GROUP BY clause for grouping the rowsUses the OVER clause to create a window frame
Rows are collapsed into a single summary rowRows are not collapsed. Instead, it keeps individual rows and adds a summary column
Aggregate functions can be used as window functions.Window functions can not be used as aggregate functions.
Aggregate functions always work on a fixed group of values.Window functions work on fixed values as well as sliding windows.

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.

Table Descriptions
Table Descriptions
Table Data
Table 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)
Aggregate Functions Example
Aggregate Functions Example

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.

Marks Table Description
Marks Table Description
Marks Table Data
Marks Table Data

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)
Ranking Students Highest First
Ranking Students Highest First

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