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.

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.

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.

Thank you very much! Worked like a charm :)