SQL command for calculating Ave from multiple tables

Expand Post »

I want to calculate average of grades of each student with SQL command but I encounter problem with this code. I have 3 tables related to each other and want to calculate average of student grades. This is my SQL string but it dose not work.
What is the problem with this code? Please take a look at it.

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
Stri = "select student.StudentID,finalgrades.finalgrade, avg(FinalGrade)" _
& "FROM (student INNER JOIN CourseStudentJunc ON Student. StudentID = CourseStudentJunc.StudentID)" _
& " INNER JOIN FinalGrades on CourseStudentJunc.JuncID = FinalGrades.JuncID group by student. StudentID "
blStudent=student info(StudentID,name,...) 
tblcourseStudentJunc=Junction table for creating many to many relationship between course and student(JuncID,StudentID,CourseID) 
tblFinalGrades=saving grades of each student for each course separately (Juncid,Finalgrade)
Stri = "select student.StudentID,finalgrades.finalgrade, avg(FinalGrade)" _
& "FROM (student INNER JOIN CourseStudentJunc ON Student. StudentID = CourseStudentJunc.StudentID)" _
& " INNER JOIN FinalGrades on CourseStudentJunc.JuncID = FinalGrades.JuncID group by student. StudentID "
blStudent=student info(StudentID,name,...) 
tblcourseStudentJunc=Junction table for creating many to many relationship between course and student(JuncID,StudentID,CourseID) 
tblFinalGrades=saving grades of each student for each course separately (Juncid,Finalgrade)

Here is my requirement of this code:
1) First of all joining these tables
2) Calculating average of grades for each student

Similar Threads

Calculating an average via multiple tables in MySQL
Submit data to multiple tables in PHP
Search from multiple tables ?? in PHP
Sql command to add new data using store values? in C#
data from multiple tables in VB.NET

Afi83

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

35 posts
since Apr 2008

Permalink

Oct 16th, 2009

Re: SQL command for calculating Ave from multiple tables

kindly post your table structures.

debasisdas

Featured Poster

Reputation Points: 665

Solved Threads: 427

Posting Genius

Offline

6,406 posts
since Feb 2007

Permalink

Nov 11th, 2009

Re: SQL command for calculating Ave from multiple tables

Hi I have a similar problem around these lines...

I have following tables:
class( class_id, class_name)
student(student_id, student_name, class_id)
exam ( exam_id , class_id , exam_date )
grade( exam_id , student_id, grade )

create a list of students and ther avg grade in their last N ( n can be any number ) tests

Any help in getting this sql will be useful

coolguy_krkr

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Nov 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: VB6 find and replace

Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: saving image in ms access using vb6

DaniWeb Message

Cancel Changes

User Name
Password