嵌套聚合函数-SQL [英] Nesting Aggregate Functions - SQL

问题描述

我想进行一个SQL查询，以查找平均评分最高的电影的奖项类别，因此，对于一组获得特定奖项的电影，如果它们的平均评分高于其他任何奖项，

I want to make a SQL query which finds the catagory of awards for movies which has the highest average rating, so for a group of movies which have won a particular award, if they have a higher average rating than any other awards group of movies then it will be returned.

我尝试过类似的操作：

SELECT MAX(AVG(m."Rating"))
FROM awards a, movies m
WHERE a."Title" = m."Title"
GROUP BY a."Award"

，但是似乎不能嵌套聚合函数。如何在每个类别的平均评分上调用max函数？

but it seems that aggregate functions cannot be nested. How can I call the max function on the average ratings for each catagory?

推荐答案

如果您只对值本身感兴趣，请执行以下操作：

If you are only interested in the value itself, the following should do it:

SELECT MAX(avg_rating)
FROM (
    SELECT AVG(m."Rating") as avg_rating
    FROM awards a, movies m
    WHERE a."Title" = m."Title"
    GROUP BY a."Award"
) t

否则，Adrian的解决方案会更好。

Otherwise Adrian's solution is better.

这篇关于嵌套聚合函数-SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！