Help with SQL query (Calculate a ratio between two entitiess)

[Mestika]

Hi,

I’m going to calculate a ratio between two entities but are having some trouble with the query.

The principal is the same to, say a forum, where you say:
A user gets points for every new thread. Then, calculate the ratio of points for the number of threads.

Example:
User A has 300 points. User A has started 6 thread. The point ratio is: 50:6

My schemas look as following:

student(studentid, name, class, major)
course(courseid, coursename, department)
courseoffering(courseid, semester, year, instructor)
faculty(name, office, salary)
gradereport(studentid, courseid, semester, year, grade)

The relations is a following:

Faculity(name) = courseoffering(instructor)
Student(studentid) = gradereport (studentid)
Courseoffering(courseid) = course(courseid)
Gradereport(courseid) = courseoffering(courseid)

I have this query to select the faculty names there is teaching one or more students:

SELECT COUNT(faculty.name) FROM faculty, courseoffering, gradereport, student WHERE faculty.name = courseoffering.instructor AND courseoffering.courseid = gradereport.courseid AND gradereport.studentid = student.studentid

My problem is to find the ratio between the faculty members salary in regarding to the number of students they are teaching.

Say, a teacher get 10.000 in salary and teaches 5 students, then his ratio should be 1:5.

Hope that someone has an answer to my problem.

Thanks
Mestika

[svmvishnu]

please try thus

select count(studentid), c.instructor,f.salary from gradereport as g inner join courseoffering as c on c.courseid = g.courseid inner join faculty as f on f.name = c.instructor group by f.name;