mysql query & the php - how to do this?

Okay, so I have a database which has the following rows, which contains what the user rating for the questions (its a rating script - th开发者_JAVA技巧is is all finished, q1 all the way to q20):

id
sid
q1
q2
q3
q4
q5
...
q19
q20

Image of table: http://i.stack.imgur.com/SM70j.jpg

Here is example row A:

id   1
sid  2447
q1   1.5
q2   2.5
q3   0.5
q4   4.5
q5   3.5
...
q19  2.0
q20  2.0

Here is example row B:

id   2
sid  2447
q1   2.5
q2   1.5
q3   1.5
q4   3.5
q5   4.5
...
q19  1.0
q20  1.0

Basically, I want a query which does the following:

It counts the data and groups it by sid, I want all of the q1 to q20 rows added up:

So q1 will be 4, q2 will be 4, q3 will be 2 and so on.

I then want to divide these by how many rows there are, so q1 will become 2, q2 will become 2, q3 will become 1 and so on. (To get the average number).

We have a page that will display this information on, so for example:

Avg rating for Accommodation = row1['q1'] + row2['q1'] / amt of rows

Avg rating for Scenery = row1['q2'] + row2['q2'] / amt of rows

Avg rating for Food = row1['q3'] + row2['q3'] / amt of rows

I have 20 questions which people rate them on, this is all coded, the data is all storing.

This is to show the data on page. I currently have it set out like

Avg rating for Accommodation

Avg rating for Scenery

Avg rating for Food

I just need help doing the mySQL query to grab the total of q1 where sid = X, divided by the amt of rows for the average rating.

I then want to pass these to variables, for example:

$r['question1'] = ...;

So that we can simply do

echo "Avg rating for Accommodation = " . $r['question1'];

Sorry if I explained it as if you were stupid, but I'm trying to be as clear as I can to how I want it so I can get help achieving this.

Any help would be greatly appreciated.

You can do this with MYSQL:

SELECT AVG(q1) as accommodation, AVG(q2) as scenery, AVG(q3) as food FROM my_table GROUP BY sid 

and so on.

To get average value, group the rows on sid and use AVG function

SELECT sid,AVG(q1) AS q1,AVG(q2) AS q2,[...] FROM <RATING_TABLE> GROUP BY sid