Clever Geek Handbook

Temp table has no error using nested aggregation

I am 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 am getting the following error:

Database query error: table "database.t" does not exist

Can anyone tell me what I am doing wrong?

Thank!

+3


source to share


1 answer


You cannot reuse T

. Something like this is possible:



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
)
+2


source






More articles:


All Articles