Temp table doesn't exist error using nested aggregation

Question

I'm trying to use nested aggregation for one of my queries:

SELECT t.type, t.avgrent
FROM (  SELECT e.type, AVG(e.rental_rate) AS avgrent
        FROM Equipment e
        GROUP BY e.type) AS t
WHERE t.avgrent IN (SELECT MIN(t.avgrent) FROM t)

And I keep getting the following error:

DB query error: Table 'database.t' doesn't exist

Can anyone tell me what I'm doing wrong?

Thanks!

Answer 1

You can not reuse T like that. Something like this maybe:

SELECT t.type, t.avgrent
FROM (  SELECT e.type, AVG(e.rental_rate) AS avgrent
        FROM Equipment e
        GROUP BY e.type) AS t
WHERE
    t.avgrent IN
(
    SELECT
        MIN(t2.avgrent)
    FROM
    (
        SELECT 
           e.type, 
           AVG(e.rental_rate) AS avgrent
        FROM Equipment e
        GROUP BY e.type
    ) AS t2
)