Results 1 to 12 of 12

Thread: Subquery

  1. #1
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58

    Unanswered: Subquery

    I have three tables, horse, entry and prize.
    I need to find the horses which have won above average total winnings.
    My query to find the average is:

    Prize
    Event_id Place Money
    101 1 120
    101 2 60
    101 3 30
    102 1 10
    102 2 5
    102 3 2
    103 1 100
    103 2 60
    103 3 40
    401 1 1000

    Horse
    Horse_id Horse_Name
    101 Flash
    102 Star
    201 Boxer
    301 Daisy
    401 Snowy
    501 Bluebell
    502 Sally
    9998 Unknown dam
    9999 Unknown sire

    Entry
    Event_id Horse_id Place
    101 101 1
    101 102 2
    101 201 3
    101 301 4
    102 201 2
    103 201 3
    201 101 1
    301 301 2
    401 102 7

    This is where I'm up to with help to date. The problem is that this solution calculates the average of all prize money available from the prize table. What I need to calculate is the average prize money per horse based on the money actually won by my horses. The following returns an empty set because no horse has earned above the average of all prize money available:

    select x.horse_id
    , x.horse_name
    , x.career_winnings
    from (select money.total_money/hors.total_horse as avg_money
    from (select count(*) as total_horse from horse) as hors
    ,(select sum(money) as total_money from prize) as money) as sum_avg
    ,(select h.horse_id
    , h.horse_name
    , SUM(p.money) as career_winnings
    from horse H
    join entry e
    on h.horse_id = e.horse_id
    join prize p
    on e.place = p.place
    and e.event_id = p.event_id
    where h.horse_name not in ('unknown dam' , 'unknown sire')
    group by h.horse_id) as x
    where x.career_winnings > sum_avg.avg_money

    The thought is that I need to move the average calculation further down in a nested subquery but I'm floundering a little here. My query returns all three horses, which is incorrect, so there's something going wrong with my calculation:

    select x.horse_id
    , x.horse_name
    , x.career_winnings
    from (select money.total_money/hors.total_horse as avg_money
    from (select count(*) as total_horse from horse) as hors
    ,(select sum(money) as total_money from prize p
    join entry e
    on e.event_id = p.event_id
    and e.place = p.place
    join horse h
    on h.horse_id = e.horse_id) as money) as sum_avg
    ,(select h.horse_id
    , h.horse_name
    , SUM(p.money) as career_winnings
    from horse H
    join entry e
    on h.horse_id = e.horse_id
    join prize p
    on e.place = p.place
    and e.event_id = p.event_id
    where h.horse_name not in ('unknown dam' , 'unknown sire')
    group by h.horse_id) as x
    where x.career_winnings > sum_avg.avg_money;

    Any pointers would be appreciated.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    What is your definition of above average earnings? There are three ways to look at that, the total prize money divided by the number of horses defined in the system or the winnings divided by the number of horses that have won some prize money or the average of prize money per races entered.

    Looking at career earnings we have this (now which ones of these are above average earnings):

    Code:
    SELECT h.horse_id,
           h.horse_name,
           SUM(p.money) AS earnings
    FROM   horse h
           JOIN entry e
             ON h.horse_id = e.horse_id
           JOIN prize p
             ON e.event_id = p.event_id
    GROUP  BY h.horse_id,
              h.horse_name;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58

    Earnings question

    Thanks, Ronan.
    I'm defining the average as being calculated from "the winnings divided by the number of horses that have won some prize money" excluding the winnings for events and places in the prize table which do not appear in the entry table.
    I ran your query and wow! I surprised myself in spotting why it gave an incorrect answer: it needed to be joined on place as well as event_id (thank you for challenging me!):

    So now I've tried embedding that subquery but got the error message:
    error 1248 (42000): Every derived table must have its own alias.
    So I'm missing something or I've nested the subquery incorrectly:

    SELECTx.horse_id,
    x.horse_name,
    x.earnings
    FROM(SELECTmoney.total_money/hors.total_horse AS avg_money
    FROM(SELECT COUNT(*) AS total_horse FROM horse) AS hors,
    (SELECT SUM(money) AS total_money FROM prize p
    JOIN entry e
    ON e.event_id = p.event_id
    AND e.place = p.place
    JOIN horse h
    ON h.horse_id = e.horse_id) AS money) AS sum_avg,
    (SELECT h.horse_id,
    h.horse_name,
    SUM(p.money) AS earnings
    FROM horse h
    JOIN entry e
    ON h.horse_id = e.horse_id
    JOIN prize p
    ON e.event_id = p.event_id
    AND e.place = p.place
    GROUP BY h.horse_id,
    h.horse_name)
    WHERE h.horse_name not in ('unknown dam' , 'unknown sire')
    GROUP BY h.horse_id)) as x
    WHERE x.earnings > sum_avg.avg_money;

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    You get this message when you use an SQL statement with "SELECT fields FROM (SELECT ...)". The second SELECT statement must have an alias such as "SELECT fields FROM (SELECT ...) x".
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58

    Derived table

    I've checked and checked my statements, focusing on the brackets particularly, looking for where I don't have an alias. Assuming, of course, that the bulk of the query is going well.
    I suspect it's in this area:

    GROUP BY h.horse_id,
    h.horse_name)
    WHERE h.horse_name not in ('unknown dam' , 'unknown sire')
    GROUP BY h.horse_id)) as x
    WHERE x.earnings > sum_avg.avg_money;

    (with the double brackets) but I'm having trouble working out how qualifying an additional alias here would then be referred to in the query.

  6. #6
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    This select statement does not have an alias

    Code:
    (SELECT h.horse_id,
    h.horse_name,
    SUM(p.money) AS earnings
    FROM horse h
    JOIN entry e
    ON h.horse_id = e.horse_id
    JOIN prize p
    ON e.event_id = p.event_id
    AND e.place = p.place
    GROUP BY h.horse_id,
    h.horse_name)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  7. #7
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58
    select x.horse_id
    , x.horse_name
    , x.earnings
    from (select money.total_money/hors.total_horse as avg_money
    from (select count(*) as total_horse from horse) as hors
    ,(select sum(money) as total_money from prize p
    join entry e
    on e.event_id = p.event_id
    and e.place = p.place
    join horse h
    on h.horse_id = e.horse_id) as money) as sum_avg
    ,(SELECT h.horse_id,
    h.horse_name,
    SUM(p.money) AS earnings
    FROM horse h
    JOIN entry e
    ON h.horse_id = e.horse_id
    JOIN prize p
    ON e.event_id = p.event_id
    AND e.place = p.place
    where h.horse_name not in ('unknown dam' , 'unknown sire')
    GROUP BY h.horse_id,
    h.horse_name) as alias ) as x
    where x.earnings > sum_avg.avg_money;

    That was the one where I thought I'd spotted the problem. So I removed the extra bracket, using x as the alias, but, of course, that's incorrect because it just processes the subquery. Then I gave the select statement an alias, and it then gave an
    ERROR near ') as x where x.earnings > sum_avg.avg_money;

    What I need to understand is where the alias from this select statement will be or should be used in the outer query, so I can understand what to call it.

  8. #8
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi,

    the way to view it is that you are creating two tables (via subqueries). The first is going to determine the average earnings of each horse. The other table/subquery is going to determine each horses earnings.

    Keeping that in mind it should help you determine the breakdown of the query:

    SELECT horses.horse_id, horses.horse_name, horses.earnings
    FROM (SELECT calculating average earnings) AS Average_earnings,
    (SELECT horse_id, horse_name, SUM(money) ...) AS horses
    WHERE horses.earnings > average_earnings.money ...;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  9. #9
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58
    Excellent explanation - thanks, Ronan. I really do understand it now.
    However, I'm still getting the syntax error before the alias statement for the second subquery:
    ERROR ... for the right syntax to use near ') as x where ...'

    I believe my alias statements for the created tables are now correct, and I can't see where I've made an error with the ') as horses ) as x...' statement.

    select x.horse_id
    , x.horse_name
    , x.earnings
    from (select money.total_money/hors.total_horse as avg_money
    from (select count(*) as total_horse from horse) as hors
    ,(select sum(money) as total_money from prize p
    join entry e
    on e.event_id = p.event_id
    and e.place = p.place
    join horse h
    on h.horse_id = e.horse_id) as money) as sum_avg
    ,(SELECT h.horse_id,
    h.horse_name,
    SUM(p.money) AS earnings
    FROM horse h
    JOIN entry e
    ON h.horse_id = e.horse_id
    JOIN prize p
    ON e.event_id = p.event_id
    AND e.place = p.place
    where h.horse_name not in ('unknown dam' , 'unknown sire')
    GROUP BY h.horse_id,
    h.horse_name) as horses) as x
    where horses.earnings > sum_avg.avg_money;

  10. #10
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Your parenthesis do not match up.

    Code:
    SELECT horses.horse_id,
           horses.horse_name,
           horses.earnings
    FROM   (SELECT money.total_money / hors.total_horse AS avg_money
            FROM   (SELECT COUNT(*) AS total_horse
                    FROM   horse) AS hors,
                   (SELECT SUM(money) AS total_money
                    FROM   prize p
                           JOIN entry e
                             ON e.event_id = p.event_id
                                AND e.place = p.place
                           JOIN horse h
                             ON h.horse_id = e.horse_id) AS money) AS sum_avg,
           (SELECT h.horse_id,
                   h.horse_name,
                   SUM(p.money) AS earnings
            FROM   horse h
                   JOIN entry e
                     ON h.horse_id = e.horse_id
                   JOIN prize p
                     ON e.event_id = p.event_id
                        AND e.place = p.place
            WHERE  h.horse_name NOT IN ( 'unknown dam', 'unknown sire' )
            GROUP  BY h.horse_id,
                      h.horse_name) AS horses
    WHERE  horses.earnings > sum_avg.avg_money;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  11. #11
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58

    Success

    Thanks so much, Ronan.
    I had actually tried that and looked at the answer and decided it was wrong, so I thought the query wasn't working, forgetting that I needed to get the average of all horses in my mental verification of the answer. Thank you so much for perservering with my questions and for all the tutoring points.
    Regards,
    Mel.

  12. #12
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    No worries!! Good luck with your project.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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