You are writing a SELECT query and you need to filter rows out of the result set based on the result of an aggregate predicate.

Which clause of the SELECT query should you put the predicate in?

Question

You are writing a SELECT query and you need to filter rows out of the result set based on the result of an aggregate predicate.

Which clause of the SELECT query should you put the predicate in?

BlackTom AI · Accepted Answer

When filtering rows based on the result of an aggregate predicate, we must consider where aggregate calculations are performed in SQL and where their conditions can be applied.
Option: ORDER BY. This clause determines the sort order of the result set. It cannot filter rows based on aggregate values; it only arranges the output after filtering has occurred. Therefore, using ORDER BY for an aggregate predicate would not achieve the filtering goal and would be inappropriate for restricting rows.
Option: GROUP BY. This clause groups rows into summary rows for aggregate calculations. While it is involved in aggregation, it is not the place to apply a filter on the aggregated results themselves. GROUP BY defines how rows are aggregated, but the filtering condition on the aggregate result belongs elsewhere, so this is not the correct location for an aggregate predicate.
Option: WHERE. The WHERE clause filters rows before any aggregation takes place. It operates on individual row values, not on the results of aggregate computations. If you try to filter on an aggregate result using WHERE, the condition may be evaluated before the aggregation, leading to incorrect behavior or errors. Therefore, this is not suitable for aggregate-based predicates.
Option: HAVING. The HAVING clause is specifically designed to filter groups after aggregation has been performed, allowing conditions to reference aggregate values (such as SUM, AVG, COUNT, etc.). This is the correct mechanism to apply a predicate on the result of an aggregate operation and filter the final result set accordingly.
In summary, the only clause that supports filtering after aggregation by evaluating aggregate results is HAVING, making it the appropriate choice for an aggregate predicate.

You are writing a SELECT query and you need to filter rows out of the result set based on the result of an aggregate predicate. Which clause of the SELECT query should you put the predicate in?单项选择题

类似问题

Name the Supreme Court decision that removed financial constraints on PACs.

Political scientists Joshua Kalla and David Broockman find that senior policy makers made themselves available to _____ between three and four times more often than they did to constituents.

Name the political scientist who argues that parties are designed to serve the needs of ambitious politicians (namely candidates and officeholders).

Name the political scientist who said this famous quote about the problems of pluralism: “The flaw in the pluralist heaven is that the heavenly choir sings with a strong upper-class accent.”

What is the name of the third party candidate in 2000 who was not allowed to participate in the presidential debates? Hint: We watched a video clip with him in class.

E.E. Schattshneider said, "Democracy is ________ save in terms of the parties."

Who was the most recently appointed justice to the Supreme Court?

Name the landmark Supreme Court case that struck down spending limits in federal elections.

更多留学生实用工具

智能学习助手

风格化写作助手

论文查重助手

文献引用助手

课堂转译助手

课堂笔记助手

Quiz搜索助手

学校历年真题

智能刷题助手

智能匹配练习

希望你的学习变得更简单