Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Sep 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sum of a range of numbers?

    I have been searching for a solution to my problem, but I have not been able to find one yet, which is why I am making my first post here.

    I am working on a project that lets the registered members submit a weekly worksheet with a number of multiple choice questions. Each week the number of questions changes, but there is always 2 choices to each question. For each question the member selects which answer they think is correct, and a 'confidence rating'. The confidence rating is used to score how much confidence they have that their answer choice is correct. For a sheet with 16 questions, the member has to rate each question with 1-16, and each value can only be used one time. Each sheet has a total possible points score, which is the sum of each possible rating (ie (1+2+3...14+15+16 = 136).

    For each week, and year there will be a records page that will show the number of points, and the member's 'average' for that time frame. For the 'average' I have to take the number of points the member received, and divide it by the number of points possible. I know this is really more of a percentage, but this is the way to client wants this process done.

    So, is there way to be able to figure out the number of total points possible for each sheet in mysql without having to create a new column in one of my tables with the value or use php?

    The tables are currently set up as follows:

    table: member_sheets
    sheet_id | member_id | week_id

    table: member_answers
    answer_id | sheet_id | question_id | answer_choice | answer_rating | answer_result (correct||incorrect)

    table: questions
    question_id | question_text | question_choiceA | question_choiceB | question_answer(a||b)



    Thank you in advance to anyone who can help or lend some advice.

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Let me rephrase your question so I can make sure I understand what you're asking. You want to know the total number of points possible for a sheet, which has any number of questions, and each question is worth from 1 to n, n being the number of questions on the sheet, with no number being used twice.

    Not using PHP, you can do a group by and sum the column answer_rating, but that depends on the accuracy of the member entries. If you have validation on the form so you are guaranteed a unique rating value for every question then you can do it this way.
    Code:
    SELECT sum(answer_rating) from member_answers group by b.sheet_id
    This could be wrong depending on how your data looks.

    You could also count up the number of questions for a sheet and then accumulate rating total in a loop, but that's using PHP.

  • #3
    New to the CF scene
    Join Date
    Sep 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you very much! Worked like a charm


  •  

    Posting Permissions

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