Average query with 2 rows from same table

Hi,

My first post in this group, so hope some of you can help me.

My table:
"answer"
answerID
answer(int)
questionID(int)
userID(int)

answer1 is questionID = 1
answer2 is questionID = 2
WHERE userID is the same for both answer1 and answer2

I want the average of answer1/answer2: AVG(ans1/ans2), but how?


Hope you will help me

kind regards

Re: Average query with 2 rows from same table

Does this sound way off?

Re: Average query with 2 rows from same table

wrote in message
news:1141751787.753558.201290@v46g2000cwv.googlegroups.com.. .
> I want the average of answer1/answer2: AVG(ans1/ans2), but how?

If you need to compare two rows from the same table, you need to do a
self-join.

Try something like this:

SELECT a1.userID, AVG(a1.answer/a2.answer)
FROM answer AS a1
INNER JOIN answer AS a2 ON a1.userID = a2.userID
AND a1.questionID = 1 AND a2.questionID = 2
GROUP BY a1.userID

Regards,
Bill K.

Re: Average query with 2 rows from same table

Thanks, that was perfect. Just needed a bit of change.

Regards