Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2008
    Posts
    59

    Unanswered: Counting only unique data in a query

    Hi,
    I am in the process of designing a database and am having some serious problems coming up with a way of counting the amount of enrollees in a database. This is probably because the database is a little complex. Anyway, if someone can help me with this I would be very grateful.

    The database user can create courses that can run for one day or for multiple days. Once a course is create students can be enrolled on that course. What makes this difficult is because if there is a course that runs for five days then we store five entries for that course in a table, each with the date. Then once a student enrols there ID is added to another table along with the ID of every date that that course happens, as well as a yes/no field. This means that we can keep track of attendance on all kinds of different courses.

    In addition to storing all this information we need to be able to output the data into something useful. Therefore I am starting off by simply choosing the name of the course and then trying to create a count of how many students are on there. Now I'm having many problems because if a course runs over two days and has two students, if I count the number of students I will get four because each students ID is stored next to every date of the course.

    I don't really want to write any more because I'm aware that I might not be making the best sense. I am however hoping that someone does understand this and might have a suggestion of how to fix it. The only way I can think of doing it would be to count only unique student IDs. Does anyone know how you would do a count of only unique information?

    Thanks!
    Russ

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Two methods.

    You can change the Database to use ANSII\92 SQL (IIRC) and then use:
    Code:
    COUNT(DISTINCT student_id)
    Or, you can create a derived table of distinct srudent_ids and perform a count on that.

    Option 1 is a wholesale database change and you would want to test all of your SQL to ensure everything still works. Option 2 is more difficult SQL syntax but only that query is affected.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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