why is this code not valid
use employees;
Delimiter $$
create procedure average_salary
BEGIN
SELECT AVG(total_salary)
FROM (SELECT d.emp_no, SUM(s.salary) AS total_salary
FROM dept_manager d
JOIN salaries s ON d.emp_no = s.emp_no
GROUP BY d.emp_no) ;
END $$
delimiter ;
You cannot aggregate the average salary from a sum of the salaries combined.
Hi Abdelrahman!
Thanks for reaching out.
As Ronith said you cannot use the average salary from a sum of the salaries combined. Please, use the following code:
DELIMITER$$
CREATE PROCEDURE average_salary()
BEGIN
SELECT AVG(salary)
FROM
dept_manager d
JOIN salaries s ON d.emp_no = s.emp_no
GROUP BY d.emp_no;
END $$
DELIMITER;
Hope this helps.
Best,
Tsvetelin