SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Can we go to a 48 hour day?
    Join Date
    May 2002
    Location
    MI
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with mysql query

    I have two tables: courses and schedules. Courses consists of a list of classes that a student may take and the properties of that course. (for instace the thing I am after is the # of students a class takes). It has three keys:
    course_num
    section_num
    professor

    The second table is stores the students that are assigned a couse to actually create a schedule for that student. It contains the three keys from the courses table plus a student_id field.

    What I want to do is create a php page that lists the courses that have been assigned to someone for the current semester. (the course table lists all possible courses that are offered, but not all will be assigned every semester) This way I can list how many students are in a course and compare it with the number of students a course takes.

    Problem: I think I want to do a select Distinct from the schedule table but if a course is taken by 10 studnets I only want one row for that class, but 10. Select Distinct gets me 10 rows because part of the key (the student_id) is different for each one.

    Any ideas as to how I can grab this listing of courses I need?

    Thanks
    mitechie.com
    "Techies just think a little differently
    ...at least that is what they keep telling me."

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select course_num, section_num, professor, count(*)
    from schedule
    group by course_num, section_num, professor ?

  3. #3
    Can we go to a 48 hour day?
    Join Date
    May 2002
    Location
    MI
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sweet! Thanks. I have never used the group by functions in SQL before and it seems that was what I was missing in my own attempts. Have to grab my book and do a refresher I guess.

    Thanks again.
    mitechie.com
    "Techies just think a little differently
    ...at least that is what they keep telling me."


Bookmarks

Posting Permissions

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