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

Question

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.

I hope that someone has an answer to my problem and understand what I'm having trouble with.

Thanks

Mestika

Some further explanation and examples on my problem and request:

Employee 1: Salary = 10.000 | # of courses he teaches: 3 | # of students (totaly) following thoes 3 courses: 15.

Then, Employee 1 earns 666,7 pr. each student. (i believe this is the ratio)

Employee 2: Salary = 30.000 | # of courses he teaches: 1 | # of students (totaly) following thoes 3 courses: 6.

Then, Employee 2 earns 5000 pr. each student.

Answer 1

when I look at your first example it says that 300 points for 6 threads works out to 50:6 rato. Don't you mean in your later example that 10000 salary for 5 students works out to 2000:5 ratio? not 1:5 ratio?

anyway if my understanding of your example is correct then this should be a good solution

select f.name, f.salary, count(s.studentid) as noofstudents, convert(f.salary / count(s.studentid),varchar(50)) + ':' + convert(count(s.studentid),varchar(10)) as ratio
  from faculty f
    inner join courseoffering co on f.name = co.instructor
    inner join gradereport gr on co.courseid = gr.courseid
    inner join student s on gr.studentid = s.studentid
  where co.semester = @semester
    and co.year = @year
  group by f.name, f.salary

perhaps you could expand on your question a bit if this isn't what you're looking for.

Answer 2

You are completely right that my own ratio examples don’t make sense so I will try to explain further.

What I am seeking to do is, to find out how much salary each faculty member has depending on the number of students they are teaching. I imagine that it is a simple question about dividing a faculty members salary by the number of students following a course that the member is teaching.

I get an error when I am running your query, my MySQL has a problem with the convert part (it seems) but otherwise you query is correct it seems.

I haven’t tried the convert statement before, but is it (and why) necessary to convert them? If I for each faculty member that has the correct conditions, find the number of students that are attending the course. Then take that faculty members salary and divide it by the found numbers of student?