This content has been marked as final. Show 2 replies
You didn't say how you were storing your data. Hopefully your table resembles something like:
PersonId - integer
QuestionID - integer
Answer - integer
Your primary key would be PersonID, QuestionId and each of those fields would have foreign key relationships to other tables.
Getting the average then becomes easy.
select sum(answer) / count(answer)
where questionID = ??
As far as percentages go, you are not being clear. Percentage of what?
Also, rethink this average of the averages concept. That rarely provides anything meaningful.
Ok firstly, the table is laid out like
AnswerID, ParticipantID, RaterID, SurveyID, Question1, Question2, Question3, Question4, Question5, Question6,...................., Question111, Question112, Question113
Next: Primary Key is RaterID
Now this is the part I thought I was being clear about,
This is a survey site, So there maybe upto 16 people completing a survey on say the leadership skills of thier boss, 113 questions make up the survey, these questions are fixed, the questions come under sub-headings, so say questions1-6 are for one heading, questions 7-11 are for another heading.
these sub-headings fit under three main headings,
inspiring: sub-headings 1-6
again these need to be shown as seperate percentages.
This is a very detailed report that has a number of layers that build up to a very big picture, so the need to have the avg of questions and then the group avg of questions it very important and MEANINGFUL.
So thanks for the last tip but as a wise man once said seek first to understand then to be understood.