Enjoy an ad free experience by logging in. Not a member yet? Register.

Results 1 to 3 of 3
Thread: Sum of a range of numbers?

09202006, 01:26 AM #1
 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 116, 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 (correctincorrect)
table: questions
question_id  question_text  question_choiceA  question_choiceB  question_answer(ab)
Thank you in advance to anyone who can help or lend some advice.
09202006, 02:28 AM
#2
 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.
This could be wrong depending on how your data looks.Code:SELECT sum(answer_rating) from member_answers group by b.sheet_id
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.
09212006, 03:18 AM
#3
 Join Date
 Sep 2006
 Posts
 5
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thank you very much! Worked like a charm