Greetings,
I have a table which tells me the compatibles each user has.
I would like to know what is the average number of compatibles for ALL users.
I tried this:
SELECT AVG( COUNT(*) )
FROM USER_COMPATABILITIES
WHERE COMPATIBLE
AND ACTIVE
GROUP BY COMPATABILITY_USER_ID
But it returns “Invalid use of Group function”.
Is anyone able to comment and fix my mistake ?
Thank You,
Mr. Alexander
rpkamp
May 17, 2010, 10:05pm
2
Could you post a SHOW CREATE of the table?
Greetings ScallioXTX, I have attached the create table so that you may see the structure.
The objective of the table: to display for each user (COMPATIBILITY_USER_ID) how many active compatibles does he / she have.
The objective of the query: I want to find out what is the average number of compatibles per user.
Thank you for your help, and please let me know if i am missing anything.
Kind Regards,
Mr. Alexander
r937
May 18, 2010, 3:50am
4
SELECT AVG(number_of_compatibles) AS avg_compatibles
FROM ( SELECT compatability_user_id
, COUNT(*) AS number_of_compatibles
FROM user_compatabilities
WHERE compatible
AND active
GROUP
BY compatability_user_id ) AS d
Greetings r937,
Thank you for your help. However this is not exactly what I was looking for.
This gives me the number of compatibles EACH user has, I would like to know the AVERAGE compatibles that I have per user given all the users.
So in essence it should take all the number that result from the query that you produced, sum them up and divide by the number of rows returns.
It should be a 1 number answer like 15.45 or 7.89.
is there a way to achieve it ?
Kind Regards,
Mr. Alexander
r937
May 18, 2010, 2:25am
6
SELECT compatability_user_id
, COUNT(*) AS number_of_compatibles
FROM user_compatabilities
WHERE compatible
AND active
GROUP
BY compatability_user_id