Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2010

    Unanswered: Help with SQL query (Calculate a ratio between two entitiess)Hi, Iím going to calcul


    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.

    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( FROM faculty, courseoffering, gradereport, student WHERE = 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.



  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    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 | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2010
    I got it to work, but thanks for your help. Used the following query:

    SELECT DISTINCT, 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 = ) AS "Ratio Per Student"
    FROM faculty
    ORDER BY "Ratio Per Student" DESC

  4. #4
    Join Date
    Apr 2002
    Toronto, Canada
    you "got it to work" but it isn't producing the correct answer | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts