Last answered:

25 Feb 2021

Posted on:

15 Feb 2021

0

MySQL Stored Procedure with IN parameter + Aggregate Function

Hi, 365 data science team

In this lesson of creating SQL stored procedure to calculate the average salary, we did not include a GROUP BY clause in the SQL query and yet, the output of the procedure works as intended. How did this happen? I thought from the previous lesson, we are supposed to include GROUP BY clause for the non-aggregated variables whenever we use aggregate functions in our query.

I also tried to include GROUP BY clause in the SQL query of the stored procedure, it gives the same result.

So which one is preferred in the SQL best practice? To include or not to include a GROUP BY clause eith your aggregate function in your stored procedure?


USE `employees`;
DROP procedure IF EXISTS `average_salary`;

DELIMITER $$
USE `employees`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `average_salary`(IN p_emp_no INTEGER)
BEGIN
	SELECT a.first_name, a.last_name, avg(b.salary) as avg_salary
    FROM employees a
    inner join salaries b
    on a.emp_no = b.emp_no
    where a.emp_no = p_emp_no;
END$$

DELIMITER ;
1 answers ( 0 marked as helpful)
Instructor
Posted on:

25 Feb 2021

0

Hi Jimmy!

Thanks for reaching out.

Please accept my apologies for the delayed response.

To make sure we are on the same page, can you please support your question with the query where you have used GROUP BY as well? Thank you.

Looking forward to your answer.
Best,
Martin

Submit an answer