Average query with 2 rows from same table

[jenner4000]

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

[jenner4000]

Does this sound way off?

[Bill Karwin]

<je********@gmail.com> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.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.

[jenner4000]

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

Regards