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.

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):

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;

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".

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.

This select statement does not have an alias

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.

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

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;

Your parenthesis do not match up.

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.

No worries!! Good luck with your project.