Q:

Combining two queries from the same table?

I have a MySQL table that keeps records of users that answered a question, with the number of trials. It's like:
username trials
-------- ------
user1    10
user2    7
user1    20

etc. From which I can calculate how many times they answered a question (COUNT).
and now I want to calculate average number of trials, i.e. get the following table:
username avg
-------- ---
user1    15
user2    7

I've tried this query:
SELECT(a.totalguess/b.totalknow) as avg FROM( SELECT username, SUM(trials) AS totalguess FROM thetable GROUP BY username) a, (SELECT username, COUNT(*) as totalknow FROM thetable GROUP BY username)b WHERE a.username=b.username;

and it gave only
avg
---
15
7

pretty close! but without the knowers. I could probably combine them with php, but I want a pure MySQL solution. So what should I do?
Thanks in advance!

A:

SELECT username as knower, AVG(trials) as theavg
FROM mytable
GROUP BY username

will give you this:
knower, theavg

user1, 15
user2, 7

A:

SELECT a.knower, (a.totalguess/b.totalknow) as avg 
FROM (SELECT knower, SUM(trials) AS totalguess FROM thetable GROUP BY knower) a, 
     (SELECT knower, COUNT(*) as totalknow FROM thetable GROUP BY knower)b 
WHERE a.knower=b.knower;

or simply:
SELECT knower, SUM(trials)/COUNT(*) as avg -- or you can just use AVG(trials)
FROM thetable 
GROUP BY knower

