Mysql, SubQuery problems

Select rating_id, average_rating

From (Select rating_id, avg(rating_num) as average_rating
    from ratings
    group by rating_id
    having count(*) > 50)

HAVING average_rating > 4 ;

After running the query, I get an error

Every derived table must have its own alias

开发者_开发百科

I know that the section here works:

Select rating_id, avg(rating_num) as average_rating
        from ratings
        group by rating_id
        having count(*) > 50

What am I doing wrong in this subquery? I searched and searched and searched but couldn't find the mistake, no matter where I corrected, I still get errors

---

As the error message states you need to add an alias for your subquery:

SELECT rating_id, average_rating
FROM (
    SELECT
        rating_id,
        AVG(rating_num) AS average_rating
    FROM ratings
    GROUP BY rating_id
    HAVING COUNT(*) > 50
) AS some_alias
WHERE average_rating > 4 

The some_alias can be anything - either a descriptive name for the subquery, or else since you never need to refer to the subquery by name you can just use non-descriptive names such as T1 (then T2, T3 etc. if you have other subqueries).

Also you can use WHERE in your outer query rather than HAVING.

---

Select rating_id, average_rating

From (Select rating_id, avg(rating_num) as average_rating
    from ratings
    group by rating_id
    having count(*) > 50) a

HAVING average_rating > 4 ;

Note the table alias "a"

---

Put "as SomeAlias" after the subquery:

Select rating_id, average_rating

From (Select rating_id, avg(rating_num) as average_rating
    from ratings
    group by rating_id
    having count(*) > 50) as A

HAVING average_rating > 4 ;