Table doesn't exist in nested query with aggregations

Question

My database name is test, I have a table named HaveGoal. I am querying this:

SELECT Rel.total
FROM (SELECT H.number, H.teamname, SUM(H.numberofgoals) AS total
FROM HaveGoal H GROUP BY H.number,H.teamname) AS Rel
WHERE Rel.total = (SELECT MAX(Rel.total) FROM Rel)

It gives:ERROR 1146 (42S02): Table 'test.Rel' doesn't exist

Answer 1

It seems that the last subselect cannot reference a nested query defined in the FROM clause.

In this case you have multiple solutions :

• Duplicate the first subselect inside the second (and hope that performance will not be too poor)
• Define a view to make the nested query available everywhere
• As you are looking for the maximum, you could sort data and take only the first line
• If you where not on MySQL, you could use the WITH statement

Duplicating will work in any situation :

SELECT Rel.total
FROM (
    SELECT H.number, H.teamname, SUM(H.numberofgoals) AS total
    FROM HaveGoal H 
    GROUP BY H.number,H.teamname
) AS Rel
WHERE Rel.total = (
    SELECT MAX(Rel2.total) 
    FROM (
        SELECT H.number, H.teamname, SUM(H.numberofgoals) AS total
        FROM HaveGoal H 
        GROUP BY H.number,H.teamname
    ) AS Rel2
)

Taking the first line after sorting is much shorter, but the MAX is implied :

SELECT Rel.total
FROM (
    SELECT H.number, H.teamname, SUM(H.numberofgoals) AS total
    FROM HaveGoalTest H 
    GROUP BY H.number,H.teamname
) AS Rel
ORDER BY total DESC
LIMIT 1