OVER Clause in MySQL

Back to: MySQL Tutorials for Beginners and Professionals

OVER Clause in MySQL with Examples

In this article, I am going to discuss the OVER Clause in MySQL with examples. At the end of this article, you will understand the OVER Clause in MySQL with Examples.

OVER Clause in MySQL:

The OVER clause in MySQL is used with the PARTITION BY clause to break the data into partitions. Following is the syntax of the OVER clause in MySQL.

The specified function is going to operate for each partition. Please have a look at the below example for a better understanding. Let say we have three departments (HR, IT, Payroll).

COUNT (Department) OVER (PARTITION BY Department) 

In the above example, the data will be partition by Department i.e. there will be three partitions (IT, HR, and Payroll), and then the COUNT() function will be applied to each partition. Here, you can use a wide range of built-in functions such as COUNT(), SUM(), MAX(), ROW_NUMBER(), RANK(), DENSE_RANK(), AVG(), MIN(), etc. If this is not clear at the moment, then don’t worry, we will see examples of each function and try to understand the need and power of the OVER Clause in MySQL.

Example: OVER clause in MySQL

We are going to use the following Employee table to understand the need and use of the Over clause in MySQL.

Please use the below SQL Script to create the database and Employees table and populate the Employees table with sample data.

CREATE DATABASE EmployeeDB;
USE EmployeeDB;

CREATE TABLE Employees
(
     ID INT PRIMARY KEY,
     Name VARCHAR(50),
     Department VARCHAR(50),
     Salary int
);

INSERT INTO Employees Values (1001, 'Sambit', 'IT', 15000);
INSERT INTO Employees Values (1002, 'Santosh', 'IT', 35000);
INSERT INTO Employees Values (1003, 'Manoj', 'HR', 15000);
INSERT INTO Employees Values (1004, 'Rakesh', 'Payroll', 35000);
INSERT INTO Employees Values (1005, 'Hina', 'IT', 42000);
INSERT INTO Employees Values (1006, 'Bikash', 'HR', 15000);
INSERT INTO Employees Values (1007, 'Priya', 'HR', 67000);
INSERT INTO Employees Values (1008, 'Preety', 'Payroll', 67000);
INSERT INTO Employees Values (1009, 'Priyanka', 'Payroll', 55000);
INSERT INTO Employees Values (1010, 'Anurag', 'Payroll', 15000);
INSERT INTO Employees Values (1011, 'Rishav', 'HR', 55000);
INSERT INTO Employees Values (1012, 'Dravid', 'IT', 96000);

Example:

Our business requirement is to generate a report that should display the total number of employees in each department. Along with the total number of employees in each department, we also need to display the Total Salary, Average Salary, Minimum Salary, and Maximum Salary of each department. That means, our business requirement is to generate a report as shown in the below image.

We can easily achieve the above output simply by using the Aggregate function along with the GROUP BY clause in MySQL. The below SQL Query will give you the desired output.

SELECT  Department, 
 COUNT(*) AS TotalEmployees, 
 SUM(Salary) AS TotalSalary,
 AVG(Salary) AS AverageSalary,
 MIN(Salary) AS MinSalary, 
 MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY Department;

Example:

Now our business requirement changes. Now we also want to show the non-aggregated values (Name and Salary) in the report along with the aggregated values as shown in the below image.

Now, you may be intended to use the below SQL query by adding the Salary, Name column in the select clause. But this is not going to be work.

SELECT Name, Salary, Department, 
 COUNT(*) AS TotalEmployees, 
 SUM(Salary) AS TotalSalary,
 AVG(Salary) AS AverageSalary,
 MIN(Salary) AS MinSalary, 
 MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY Department;

When you execute the above query, you will get the following output. It randomly shows one employee data of each department.

How to achieve the desired output?

We can get the desired output in two ways.

Solution1:

One of the ways to get the desired output is by including all the aggregations in a subquery and then JOINING that subquery with the main query. The following example exactly does the same.

SELECT Name, Salary, Employees.Department, 
 Departments.TotalEmployees,
 Departments.TotalSalary, 
 Departments.AverageSalary, 
 Departments.MinSalary, 
 Departments.MaxSalary   
FROM  Employees
INNER JOIN
( SELECT Department, COUNT(*) AS TotalEmployees,
  SUM(Salary) AS TotalSalary,
  AVG(Salary) AS AverageSalary,
  MIN(Salary) AS MinSalary, 
  MAX(Salary) AS MaxSalary
 FROM Employees
 GROUP BY Department) AS Departments
ON Departments.Department = Employees.Department;

Now, execute the above query and you will get the desired output as shown in the below image. But look at the number of SQL statements that we write.

Solution2:

The second way is the most preferable way to get the desired output is by using the OVER clause combined with the PARTITION BY clause as shown in the below code.

SELECT Name, Salary, Department,
 COUNT(Department) OVER(PARTITION BY Department) AS TotalEmployees,
 SUM(Salary) OVER(PARTITION BY Department) AS TotalSalary,
 AVG(Salary) OVER(PARTITION BY Department) AS AverageSalary,
 MIN(Salary) OVER(PARTITION BY Department) AS MinSalary,
 MAX(Salary) OVER(PARTITION BY Department) AS MaxSalary
FROM Employees;

Once you execute the above query, you will get the output as expected as shown in the below image.

In the next article, I am going to discuss the ROW_NUMBER function in MySQL with examples. Here, in this article, I try to explain the need and use of the OVER Clause in MySQL with Examples. I hope you enjoy this OVER Clause in the MySQL article.