开发者

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 ;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜