How can I fix my stored procedure to return the average GPA across all sections that a certain professor teaches?

[r937]

@sealsd

What I am missing or not doing right?

which professor

[Mittineague]

Don’t you need a GROUP BY in there somewhere?

https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_avg

[sealsd]

@r937,

I know you are proabably getting tired of me, but I feel like I am getting close to figuring it out. Richard is the professor who has a FacultyID of 2. I have shown you that already.

[sealsd]

@Mittineague,

Do you think you can help? How can I write a single query that will output the average gpa of all students that the professor taught. To answer your question, I don’t think I would need to include a GROUP BY because it’s only a single column name that is selected on. I am breaking it down into smaller steps in order to put that select statement into my stored procedure.

[sealsd]

@r937,

Will this stored procedure work like this? I think it does because it does return the average gpa for all the sections he taught students in. Also, when I run the single query below and then input that query into the stored procedure, they basically produce the same thing. Here is the code for single query run, and the code also for when I input that query into the stored procedure,

USE COLLEGE; 

SELECT 
AVG(Registration.Grade) 
FROM 
Registration 
INNER JOIN 
Section ON Registration.SectionID= Section.ID
WHERE TaughtByID= 2; 

– Create a Stored Procedure named Faculty_GPA to return the AverageGPA of students across all
– the sections that the professor teaches.

  USE COLLEGE; 
  
  DROP PROCEDURE IF EXISTS Faculty_GPA ;

DELIMITER $$

CREATE PROCEDURE Faculty_GPA(IN FacultyID INT, OUT outavgGPA decimal(4, 2))

BEGIN
         DECLARE theGPAInfo DECIMAL(4,2); 
         
		 SET theGPAInfo= 
         (SELECT AVG(Registration.Grade) 
			   FROM 
		    Registration 
              INNER JOIN 
             Section ON Registration.SectionID= Section.ID
		    WHERE section.TaughtByID= 2); 
            
		
     SET outavgGPA= theGPAInfo; 
END $$

DELIMITER ;


CALL Faculty_GPA(2, @averageGPA); 



SELECT @averageGPA as GPA; 

[Mittineague]

@sealsd

I don’t think I would need to include a GROUP BY because it’s only a single column name that is selected on.

I don’t know. I guess as long as there is only one “section id” probably not.

If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows.

[r937]

@Mittineague

Don’t you need a GROUP BY in there somewhere?

nope!!

[r937]

@sealsd

Richard is the professor who has a FacultyID of 2. I have shown you that already.

yup, but you need to confirm that your query works correctly

[r937]

@sealsd

CALL Faculty_GPA(2, @averageGPA);

try calling it with a different faculty id and see if the results change

[sealsd]

@r937, @Mittineague

I solved it guys. Thanks for all help and making me think harder than I have ever had to. Especially you @r937. Oh @r937, I don’t think I need to change that argument for the calling procedure because if I do that, it won’t display the average gpa across all the sections that are taught by professor Richard. It would do it for some other Faculty Member instead. See I did my research @r937. I hope you take that as a joke. Programmers seem so serious now of days. Here is my final solution to the problem

-- Create a Stored Procedure named Faculty_GPA to return the AverageGPA of students across all
-- the sections that the professor teaches. 

  USE COLLEGE; 
  
  DROP PROCEDURE IF EXISTS Faculty_GPA ;

DELIMITER $$

CREATE PROCEDURE Faculty_GPA(IN FacultyID INT, OUT outavgGPA decimal(4, 2))

BEGIN
         DECLARE theGPAInfo DECIMAL(4,2); 
         
		 SET theGPAInfo= 
         (SELECT AVG(Registration.Grade) 
			   FROM 
		    Registration 
              INNER JOIN 
             Section ON Registration.SectionID= Section.ID
		    WHERE section.TaughtByID= FacultyID); 
            
		
     SET outavgGPA= theGPAInfo; 
END $$

DELIMITER ;


CALL Faculty_GPA(2, @averageGPA); 



SELECT @averageGPA as GPA;