Insert Into errors

[Donovan]

I have a query where I'm trying to insert course grades. 

 

A course can have several "sessions" where students participate in "Team Learning".  During the all day course they take 3 exams.  The whole course may be 3-4 weeks long where they may have 3 or 4 or more "Team Learning" sessions (or TL sessions for short).

 

So I have a courses table with Course_ID.

 

I have a session table with Session_ID and Course_ID

I have a session_grades table with Session_ID and a SOMS_KEY (SOMS_KEY for each student), and a TL_Avg for each grade for that session.

 

My query looks like this:

 

$insertgrades = $db->sql_query("INSERT INTO ".$prefix."_tl_course_grades (Course_ID, SOMS_KEY, UID, Academic_Year, Final_Grade) SELECT s.Course_ID, g.SOMS_KEY, g.UID, g.Academic_Year, AVG(TL_Avg) AS Final_Grade
FROM ".$prefix."_tl_session_grades g
JOIN ".$prefix."_tl_session s ON (g.Session_ID = s.Session_ID)
JOIN ".$prefix."_tl_courses c ON (s.Course_ID = c.Course_ID)
WHERE c.Course_ID = '$Course_ID'
GROUP BY g.SOMS_KEY");
if (!$insertgrades) {
	echo("<p>Error performing query: " . mysql_error() . "</p>");
	exit();   
	}

 

For this example my course had 3 TL sessions.  I need to get the AVG (TL_Avg) for all my sessions per course and insert it into the course_grades table.

 

This is happening but it is happening 3 times, where I just need it to be inserted once.  If I need to limit the number of inserts would a simple left join work?  if so where should I put that?

[fenway]

3 times? Well, of course... issue an update statement to calculate the average afterwords.

[Donovan]

Let me explain:

 

Here is what my session_grades looks like

 

Session_ID SOMS_KEY  UID Group_ID IRAT_Raw GRAT_Raw AppEx_Raw IRAT_Grade GRAT_Grade AppEx_Grade TL_Avg Academic_Year Excused

46 202 U00376103 3 6.0 10.0 5.0 60.00 100.00 62.50 73.00 0709 0

47 202 U00376103 3 8.0 9.0 6.0 80.00 90.00 75.00 81.00 0709 0

48 202 U00376103 3 5.0 10.0 9.0 50.00 100.00 90.00 81.00 0709 0

 

I have three different Session_ID's all for the same student (SOMS_KEY).  Each session grade has been averaged and written to TL_Avg based on a weighted value.  (IRAT * .03) + GRAT *.03) + (Appex * .04) or something similar.  I now want to get the average of all three TL_Avg and write that to the course_grades table.  This is happening but I am writing the same record three different times.

 

I have to join the table back like so:

 

INSERT INTO course_grades (Course_ID, SOMS_KEY, UID, Academic_Year, Final_Grade) SELECT s.Course_ID, g.SOMS_KEY, g.UID, g.Academic_Year, AVG(TL_Avg) AS Final_Grade
FROM _session_grades g
JOIN session s ON (g.Session_ID = s.Session_ID)
JOIN courses c ON (s.Course_ID = c.Course_ID)
WHERE c.Course_ID = '$Course_ID' 

 

to make sure I am processing the correct grades for the Course I have chosen.

 

[fenway]

I still don't see why you need to do this in a single step.

[Donovan]

Why write the course grade anywhere else but the course_grades table?  How do I write a single grade to course_grades based off of three (or more) records from session_grades?

[fenway]

Why store the average at all? All I'm suggesting is that you leave out the average and get your inserts working.

[Donovan]

Ok.  I'll leave out the avg for the initial insert, but I still have multiple records in session_grades for one course where I need to insert only one record in the course_grade table for each student.  I thought if I GROUP BY SOMS_KEY I could only pull one record for each SOMS_KEY but that doesn't work.

 

$insertgrades = $db->sql_query("INSERT INTO ".$prefix."_tl_course_grades (Course_ID, SOMS_KEY, UID, Academic_Year) SELECT DISTINCT s.Course_ID, g.SOMS_KEY, g.UID, g.Academic_Year
FROM ".$prefix."_tl_session_grades g
JOIN ".$prefix."_tl_session s ON (g.Session_ID = s.Session_ID)
JOIN ".$prefix."_tl_courses c ON (s.Course_ID = c.Course_ID)
WHERE c.Course_ID = '$Course_ID'
GROUP BY SOMS_KEY");

 

I need to pull one record per student that exist in session_grades for sessions that exist in the Course I am trying to process.  once i get this working i van go back and run my UPDATE and SET the Final_Grade

[Donovan]

Do you think it is better to INSERT INTO ... VALUES(a, b, c),  instead of trying it with subqueries.

[fenway]

Let's forget about the insert for a minute... let's work on selecting the rows.

 

You have g.UID -- so distinct will likely do nothing.

[Donovan]

The UID and SOMS_KEY are used to identify the students.  UID stands for University ID, and SOMS_KEY is a School of Medicine Key used to identify them as a medical student.

 

In my students table these appear once.

 

Here is the relevant table structure of my database.

 

Table structure for table `atlas_tl_courses`

  `Course_ID` varchar(25) NOT NULL default '0',

  `Course_Name` varchar(255) NOT NULL default '',

  `Course_Number` mediumint(6) NOT NULL default '0',

 

Table structure for table `atlas_tl_session`

`Session_ID` int(11) NOT NULL auto_increment,

  `Course_ID` varchar(25) NOT NULL default '',

  `Director_ID` varchar(25) NOT NULL default '',

  `Session_Name` varchar(50) NOT NULL default '',

  `Session_Detail` varchar(50) default NULL,

  `TLDate` date NOT NULL default '0000-00-00',

  `status` set('0','1') NOT NULL default '0',

  `Calendar_Year` varchar(5) NOT NULL default '',

 

Table structure for table `atlas_tl_students`

`Class_Year` tinyint(4) default NULL,

  `Cohort` year(4) default NULL,

  `Email` varchar(30) default NULL,

  `Graduation_Year` year(4) NOT NULL default '0000',

  `Name_First` text NOT NULL,

  `Name_Last` text NOT NULL,

  `Name_Middle` text,

  `SOMS_KEY` int(11) NOT NULL default '0',

  `UID` varchar(9) default NULL,

 

Table structure for table `atlas_tl_session_grades`

`Session_ID` int(11) NOT NULL default '0',

  `SOMS_KEY` int(11) NOT NULL default '0',

  `UID` varchar(9) default NULL,

  `Group_ID` int(11) NOT NULL default '0',

  `IRAT_Grade` decimal(5,2) default '0.00',

  `GRAT_Grade` decimal(5,2) default '0.00',

  `AppEx_Grade` decimal(5,2) default '0.00',

  `Academic_Year` varchar(5) NOT NULL default '',

 

Table structure for table `atlas_tl_course_grades`

`Course_ID` varchar(25) NOT NULL default '',

  `SOMS_KEY` int(11) NOT NULL default '0',

  `UID` varchar(9) NOT NULL,

  `Academic_Year` varchar(5) NOT NULL default '', 

  `Multiplier` decimal(2,1) default '0.0',

  `Final_Grade` decimal(5,2) NOT NULL default '0.00',

 

Each course would have multiple sessions of "Team Learning"  Students get into their groups and do exercises.

 

They get 3 exams during the "TL" session, and IRAT, GRAT, and AppEx.

 

I grade these then average them against weights decided on in the beginning of the academic year. (IRAT are 30% of grade, GRAT are 40% of grade and so on.

 

Like this:

$updatetlavg = $db->sql_query("UPDATE ".$prefix."_tl_session_grades sg
JOIN ".$prefix."_tl_session s
SET TL_Avg = (SELECT ((IRAT_Grade * $irat_wt) + (GRAT_Grade * $grat_wt) + (Appex_Grade * $appex_wt)) AS TLAVG) 
WHERE sg.Session_ID = s.Session_ID");

 

Then for each course when all TL sessions are complete I need to AVG all TL session grades AVG(TL_Avg) for each student and write this to the course_grades table.

 

I want to thank you for helping me figure out my logic for this.  It is most appreciated.

[Donovan]

Made a breakthrough.

 

This query run in phpMyadmin seems to give me what I want.

 

SELECT Course_ID, SOMS_KEY, UID, Academic_Year, AVG( TL_Avg ) AS Final_Grade
FROM atlas_tl_session_grades sg
JOIN atlas_tl_session s ON ( s.Session_ID = sg.Session_ID )
WHERE s.Course_ID = 'CL00000011'
GROUP BY sg.SOMS_KEY

 

But I have syntax errors trying to come up with an INSERT INTO

 

$insertgrades = $db->sql_query("INSERT INTO ".$prefix."_tl_course_grades (Course_ID, SOMS_KEY, UID, Academic_Year, Final_Grade)". "VALUES (SELECT Course_ID, SOMS_KEY, UID, Academic_Year, AVG(TL_Avg) AS FinalGrade
FROM ".$prefix."_tl_session_grades sg
JOIN ".$prefix."_tl_session s ON (s.Session_ID = sg.Session_ID)
WHERE s.Course_ID = '$Course_ID'
GROUP BY sg.SOMS_KEY)"); 
if (!$insertgrades) {
	echo("<p>Error performing query: " . mysql_error() . "</p>");
	exit();   
	}

[fenway]

It should read:

 

$insertgrades = $db->sql_query("INSERT INTO ".$prefix."_tl_course_grades (Course_ID, SOMS_KEY, UID, Academic_Year, Final_Grade)". " SELECT Course_ID, SOMS_KEY, UID, Academic_Year, AVG(TL_Avg) AS FinalGrade

[Donovan]

How can this query:

 

SELECT Course_ID, SOMS_KEY, UID, Academic_Year, AVG( TL_Avg ) AS Final_Grade
FROM atlas_tl_session_grades sg
JOIN atlas_tl_session s ON ( s.Session_ID = sg.Session_ID )
WHERE s.Course_ID = 'CL00000011'
GROUP BY sg.SOMS_KEY

 

gives me 104 records which is what I want.

 

Yet this query inserts 312 records?

 

$insertgrades = $db->sql_query("INSERT INTO ".$prefix."_tl_course_grades (Course_ID, SOMS_KEY, UID, Academic_Year, Final_Grade)". "SELECT Course_ID, SOMS_KEY, UID, Academic_Year, AVG(TL_Avg) AS FinalGrade
FROM ".$prefix."_tl_session_grades sg
JOIN ".$prefix."_tl_session s ON (s.Session_ID = sg.Session_ID)
WHERE s.Course_ID = '$Course_ID'
GROUP BY sg.SOMS_KEY"); 
if (!$insertgrades) {
	echo("<p>Error performing query: " . mysql_error() . "</p>");
	exit();   
	}	

[fenway]

Interesting... I've never actually used GROUP BY in this type of scenario... maybe it doesn't really work?  Hmm.