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

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. Registered User
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;```

3. Registered User
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. Registered User
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".

5. Registered User
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. Registered User
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)```

7. Registered User
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. Registered User
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 ...;

9. Registered User
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. Registered User
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;```

11. Registered User
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. Registered User
Join Date
Sep 2009
Location
San Sebastian, Spain
Posts
880
No worries!! Good luck with your project.

#### Posting Permissions

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