Databases
StackOverflow2013
Postsdbo
POHelp with SQL query (Calculate a ratio between two entitiess)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHelp with SQL query (Calculate a ratio between two entitiess)
    text
    copied!<p>I’m going to calculate a ratio between two entities but are having some trouble with the query.</p> <p>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.</p> <p>Example: User A has 300 points. User A has started 6 thread. The point ratio is: 50:6</p> <p>My schemas look as following:</p> <p>student(<strong>studentid</strong>, name, class, major)</p> <p>course(<strong>courseid</strong>, coursename, department)</p> <p>courseoffering(courseid, semester, year, <strong>instructor</strong>)</p> <p>faculty(<strong>name</strong>, office, salary)</p> <p>gradereport(<strong>studentid</strong>, <strong>courseid</strong>, semester, year, grade)</p> <p><em>The relations is a following:</em></p> <p>Faculity(name) = courseoffering(instructor)</p> <p>Student(studentid) = gradereport (studentid)</p> <p>Courseoffering(courseid) = course(courseid)</p> <p>Gradereport(courseid) = courseoffering(courseid)</p> <p>I have this query to select the faculty names there is teaching one or more students:</p> <pre><code>SELECT COUNT(faculty.name) FROM faculty, courseoffering, gradereport, student WHERE faculty.name = courseoffering.instructor AND courseoffering.courseid = gradereport.courseid AND gradereport.studentid = student.studentid </code></pre> <p>My problem is to find the ratio between the faculty members salary in regarding to the number of students they are teaching.</p> <p>Say, a teacher get 10.000 in salary and teaches 5 students, then his ratio should be 1:5.</p> <p>I hope that someone has an answer to my problem and understand what I'm having trouble with.</p> <p>Thanks</p> <p>Mestika</p> <p><strong>Some further explanation and examples on my problem and request:</strong></p> <p><strong>Employee 1:</strong> Salary = 10.000 | # of courses he teaches: 3 | # of students (totaly) following thoes 3 courses: 15.</p> <p>Then, Employee 1 earns 666,7 pr. each student. (i believe this is the ratio)</p> <p><strong>Employee 2:</strong> Salary = 30.000 | # of courses he teaches: 1 | # of students (totaly) following thoes 3 courses: 6.</p> <p>Then, Employee 2 earns 5000 pr. each student.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload