#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    8
    Rep Power
    0

    Finding AVG of a COUNT


    Hello,

    I've been learning SQL for the past couple of weeks and have run into a problem I can't resolve. I am using the tutorials here: sqlzoo.net/a2m.htm - and trying to answer question seven.

    The question asks: List the name and the number of players for the band whose number of players is greater than the average number of players in each band.

    My code right now:

    SELECT band_name, COUNT(band_id) AS members
    FROM plays_in JOIN band
    ON band_id = band_no
    GROUP BY band_no
    HAVING COUNT(band_id) > (MAX(player)/MAX(band_id))
    ORDER BY members ASC;

    I first tried doing this with a WHERE clause but that returned a function error. I also tried using SUM(members) but that didn't work.

    If I input:
    SELECT MAX(player)/MAX(band_no)
    FROM plays_in JOIN band
    ON band_id = band_no;
    I get the average, it just doesn't seem to be working for this problem.

    Any help/thoughts is greatly appreciated!

    Thanks
    AH
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,260
    Rep Power
    4279
    MAX(player)/MAX(band_no) is not going to produce anything meaningful

    the solution is a three step process

    first, write a query that gives the count of players in each ban

    this will be a simple query with COUNT(*) and GROUP BY

    second, use that query as a subquery in the FROM clause, i.e. a derived table, to find the average number of players in all bands

    see if you can get this far, and then i'll help you with the last step
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    8
    Rep Power
    0
    I've been looking this over for the past hour, I think I'm not using the derived table correctly. This is what I have so far:

    Code:
    SELECT band_no, band_name
    FROM (SELECT COUNT(band_id) AS members
    FROM band JOIN plays_in
    ON plays_in.band_id= band.band_no
    GROUP BY band_no) as BMembers
    WHERE members > AVG (BMembers);
    It's telling me it doesn't recognize the "band_no" field. Tips?

    Thanks so much!
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,260
    Rep Power
    4279
    Originally Posted by deathagent12
    It's telling me it doesn't recognize the "band_no" field.
    the subquery produces a table with only one column, called members

    therefore, you cannot select band_no and band_name from that table

    make sense?

    also, you wrote AVG(BMembers), and of course you cannot calculate the average of a table

    finally, i'm wondering if you need to do the join in the subquery -- i can't see your tables, but i suspect not
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    8
    Rep Power
    0

    Okay...


    Thanks so much for all your continued help.

    I figured out how to get the AVG of members in each band, as you'd requested on your first post:

    Code:
    SELECT  AVG(members)
    FROM (SELECT COUNT(band_id) AS members
    FROM plays_in 
    GROUP BY band_id) 
    AS BMembers;
    I'm trying to figure out how to only display those that are higher:

    Code:
    SELECT band_no, band_name
    FROM (SELECT COUNT(band_id) AS members
    FROM plays_in GROUP BY band_id) AS BMembers JOIN band
    WHERE AVG(BMembers.members) < BMembers.members;
    But that's not working. Do I need to put the SELECT AVG code form above, within another subquery? I guess this is where the final step you mentioned comes in. Also the database is explained here, along with an entity-relationship chart: sqlzoo.net/a2.htm
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,260
    Rep Power
    4279
    Originally Posted by deathagent12
    I figured out how to get the AVG of members in each band, as you'd requested on your first post:

    Code:
    SELECT  AVG(members)
    FROM (SELECT COUNT(band_id) AS members
    FROM plays_in 
    GROUP BY band_id) 
    AS BMembers;
    excellent

    what you have here is the code necessary to provide the average number of members for all bands

    the final step is to stuff the above into the HAVING clause of the query which finds the number of members for each band -- similar to what you tried in post #1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    8
    Rep Power
    0
    Awesome, I've ended up with this:

    Code:
    SELECT band_name, COUNT(band_id) AS members
    FROM plays_in JOIN band
    ON band_id = band_no
    GROUP BY band_no
    HAVING COUNT(band_id) > 
    (SELECT  AVG(members)
    FROM (SELECT COUNT(band_id) AS members
    FROM plays_in 
    GROUP BY band_id) 
    AS BMembers)
    ORDER BY members ASC;
    Which seems to give the right result.

    Thank you so much for your help! Now on to the next problem.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,260
    Rep Power
    4279
    you're welcome

    thanks for hanging in there

    Comments on this post

    • deathagent12 agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo