SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast Mr.Alexander's Avatar
    Join Date
    Apr 2010
    Location
    Toronto, Canada
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Getting the AVG of a GROUP BY clause

    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

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,892
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    Could you post a SHOW CREATE of the table?

  3. #3
    SitePoint Enthusiast Mr.Alexander's Avatar
    Join Date
    Apr 2010
    Location
    Toronto, Canada
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Attached Files Attached Files

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT compatability_user_id
         , COUNT(*) AS number_of_compatibles 
      FROM user_compatabilities
     WHERE compatible
       AND active
    GROUP 
        BY compatability_user_id
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast Mr.Alexander's Avatar
    Join Date
    Apr 2010
    Location
    Toronto, Canada
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •