Getting the AVG of a GROUP BY clause

[Mr.Alexander]

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

[ScallioXTX]

Could you post a SHOW CREATE of the table?

[Mr.Alexander]

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]

SELECT compatability_user_id
     , COUNT(*) AS number_of_compatibles 
  FROM user_compatabilities
 WHERE compatible
   AND active
GROUP 
    BY compatability_user_id

[Mr.Alexander]

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]

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