3

Consider an SQL query like this

SELECT Temp.rating, Temp.avgage
FROM (SELECT S.rating, AVG (S.age) AS avgage
FROM Sailors S 
GROUP BY S.rating) AS Temp
WHERE Temp.avgage = (SELECT MIN (Temp.avgage)
FROM Temp)

MySQL monitor gives this error:

ERROR 1146 (42S02): Table 'testDB.Temp' doesn't exist

What should I do to avoid this error?

bfaskiplar
  • 865
  • 1
  • 7
  • 23

1 Answers1

1
SELECT Temp.rating, Temp.avgage 
FROM 
  ( SELECT S.rating, AVG(S.age) AS avgage
    FROM Sailors S 
    GROUP BY S.rating
  ) AS Temp
WHERE Temp.avgage = 
      ( SELECT MIN (Temp.avgage)
        FROM Temp                --- the error is here
      )

Depending on how you want to handle ties, you could just use:

SELECT Temp.rating, Temp.avgage 
FROM 
  ( SELECT S.rating, AVG(S.age) AS avgage
    FROM Sailors S 
    GROUP BY S.rating
  ) AS Temp
ORDER BY Temp.avgage 
LIMIT 1

or the simpler equivalent:

SELECT S.rating, AVG(S.age) AS avgage
FROM Sailors S 
GROUP BY S.rating
ORDER BY avgage 
LIMIT 1

or this one (that shows all tied results):

SELECT Temp.rating, Temp.avgage 
FROM 
  ( SELECT S.rating, AVG(S.age) AS avgage
    FROM Sailors S 
    GROUP BY S.rating
  ) AS Temp
WHERE Temp.avgage = 
      ( SELECT AVG(S.age) AS avgage
        FROM Sailors S 
        GROUP BY S.rating
        ORDER BY avgage 
        LIMIT 1              
      )
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • What if we have more than one such tuples? – bfaskiplar Jan 19 '12 at 11:14
  • Do you mean more than one ratings with same (minimum) average? Then use the last query. – ypercubeᵀᴹ Jan 19 '12 at 12:53
  • OK. I got it. But, I think the usage of the aggregate operation(AVG) twice is not nice and redundant. Instead, I used a view for AVG of the ages of the sailors, and dropped it after I'm done. – bfaskiplar Jan 19 '12 at 15:34
  • You can use a View (or a CTE in other DBMS, not available in MySQL), to have more elegant code. But the query that is run in the end, it's the same. – ypercubeᵀᴹ Jan 20 '12 at 13:34