Automate Salary Calculations with a MySQL Stored Procedure

Create a Stored Procedure to Calculate Average Salary

Write a MySQL query to create a stored procedure that calculates the average salary of employees in a department.

Solution:

-- Change the delimiter to allow multi-line stored procedure definition
DELIMITER //

-- Create a stored procedure named `CalculateAverageSalary`
CREATE PROCEDURE CalculateAverageSalary(IN p_DepartmentID INT, OUT p_AverageSalary DECIMAL(10, 2))  
    -- Input parameter: DepartmentID for which the average salary is calculated
    -- Output parameter: AverageSalary to return the calculated average salary

BEGIN
    -- Calculate the average salary for the department
    SELECT AVG(Salary) INTO p_AverageSalary  -- Calculate the average salary and store it into the output variable
    FROM Employees  -- From the Employees table
    WHERE DepartmentID = p_DepartmentID;  -- Filter by the input DepartmentID
END //

-- Reset the delimiter back to `;`
DELIMITER ;

Explanation:

• Purpose of the Query:
• The goal is to automate the calculation of average salary using a stored procedure.
• Key Components:
• SELECT AVG(Salary) INTO: Calculates and stores the average salary.
• OUT: Specifies an output parameter.
• Why use Stored Procedures?:
• Stored procedures encapsulate complex calculations, making them reusable.
• Real-World Application:
• For example, in a payroll system, you might use a stored procedure to calculate department averages.

For more Practice: Solve these Related Problems:

• Write a MySQL query to create a stored procedure that calculates the average budget of departments in the Departments table.
• Write a MySQL query to create a stored procedure that calculates the average duration of projects in the Projects table.
• Write a MySQL query to create a stored procedure that calculates the average age of customers in the Customers table.
• Write a MySQL query to create a stored procedure that calculates the average total amount of orders in the Orders table.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Create a Trigger to Prevent Invalid Salary Updates.
Next MySQL Exercise: Call the Stored Procedure to Calculate Average Salary.