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:

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

we'll walk you through it

first, you need to get the number of students taught by each faculty member

hint: add a GROUP BY to your query

also, a suggestion: use JOIN syntax, not the old style "comma" joins

I got it to work, but thanks for your help. Used the following query:

SELECT DISTINCT faculty.name, faculty.salary / (
SELECT COUNT( student.studentid ) AS StudentPerTeacher
FROM student, faculty, courseoffering, gradereport
WHERE student.studentid = gradereport.studentid
AND gradereport.courseid = courseoffering.courseid
AND courseoffering.instructor = faculty.name ) AS "Ratio Per Student"
FROM faculty
ORDER BY "Ratio Per Student" DESC

you "got it to work" but it isn't producing the correct answer