Weighted Averages

I have a customer survey that asks four questions regarding a helpdesk incident.

How would you rate the waiting time
How would you rate the resolution time
Was the helpdesk agent easy to understand
How do you rate the competency of the helpdesk analyst.

The customer then has the following choices per question

Excellent
Very Good
Satisfactory
Very Bad
Unacceptable

When the answers are submitted, data is inserted in to the following fields in my database.

ID - A unique ID for the survey respondentYEARMON - the year and month of the respondent 2012.01Market - Country of respondent.Q_ID - The ID of the question listed above. I.E Q_WT corresponds to wait time...Response - Response from the list of the above. I.E Very BadCustomer - The Customers name

I have been asked to calculate the average score per question. With the responses below have the following scores -

Excellent 100
Very Good 75
Satisfactory 50
Very Bad 25
Unacceptable 0

I am really unsure what the calculation I need to use to work out the average per question, grouped by month and year.

PS the survey is not live and if anyone has any recommendations over my database structure then please feel free to advise.

if you want the average per question irrespective of the month then use the below

select AVG(case Response WHEN 'Excellent' THEN 100 WHEN 'Very Good' THEN 75 WHEN 'Satisfactory' THEN 50 WHEN 'Very Bad' THEN 25 when 'Unacceptable' then 0 ELSE 0 end ) AS AVERAGE,Q_IDFROM <YOUR TABLE>GROUP BY Q_ID

if you want the average per question and per month then you can do the below

select AVG(case Response WHEN 'Excellent' THEN 100 WHEN 'Very Good' THEN 75 WHEN 'Satisfactory' THEN 50 WHEN 'Very Bad' THEN 25 when 'Unacceptable' then 0 ELSE 0 end ) AS AVERAGE.q_id,YEARMON FROM <YOUR TABLE>GROUP BY Q_ID,YEARMON

I would suggest you do not store the string responses, instead store values associated with those strings. Then "lookup" the strings if you need to. This enables you to alter/add questions, markets, responses over time. I also would store the date of a response, not just the year and month (gives you more options).