DaniWeb Forum Index > Software Development > Visual Basic 4 / 5 / 6

Visual Basic 4 / 5 / 6 RSS

SQL command for calculating Ave from multiple tables

•

Join Date: Apr 2008

Posts: 27

Reputation: Afi83 is an unknown quantity at this point

Solved Threads: 0

Afi83

Offline

Light Poster

SQL command for calculating Ave from multiple tables

Oct 15th, 2009

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.

 Help with Code Tags 
 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

•

Join Date: Feb 2007

Posts: 2,094

Reputation: debasisdas will become famous soon enough

Solved Threads: 128

debasisdas debasisdas is offline

Offline

Postaholic

Oct 16th, 2009

kindly post your table structures.

Share your Knowledge.

•

Join Date: Nov 2009

Posts: 1

Reputation: coolguy_krkr is an unknown quantity at this point

Solved Threads: 0

coolguy_krkr coolguy_krkr is offline

Offline

Newbie Poster

19 Days Ago

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