By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,346 Members | 2,331 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,346 IT Pros & Developers. It's quick & easy.

How can I fix my stored procedure to return the average GPA across all sections?

P: 74
Hello Everyone,
I have been struggling for hours with trying to figure out how I can return the average GPA across all sections that a professor teaches. Here is the code that I have so far. I know would only need Section and Registration tables in order to output this type of procedure that I want, but it is not producing the right results. Can someone please help me? Here is my ERD model attached below in case you need to know the relationships of each table.

Expand|Select|Wrap|Line Numbers
  1.  USE COLLEGE; 
  2.  
  3.   DROP PROCEDURE IF EXISTS Faculty_GPA ;
  4.  
  5. DELIMITER $$
  6.  
  7. CREATE PROCEDURE Faculty_GPA(IN FacultyID INT,  OUT outavgGPA decimal(4, 2))
  8.  
  9. BEGIN
  10.          DECLARE theGPAInfo DECIMAL(4,2); 
  11.  
  12.          SET theGPAInfo= 
  13.          (SELECT AVG(registration.Grade)
  14.             FROM 
  15.             Registration
  16.             INNER JOIN 
  17.              Student ON registration.ID= Student.ID
  18.              INNER JOIN 
  19.              Section ON Student.ID= Section.ID
  20.              WHERE section.ID= 2 AND section.ID= 3);
  21.  
  22.      SET outavgGPA= theGPAInfo; 
  23. END
  24. $$
  25.  
  26. DELIMITER ;
  27.  
  28.  
  29. CALL Faculty_GPA(2, @averageGPA); 
  30.  
  31.  
  32. SELECT @averageGPA as GPA; 
  33.  
Attached Images
File Type: jpg screenshot1.jpg (48.0 KB, 205 views)
Apr 10 '17 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.