In aggregation queries, what does the HAVING clause do, and how does it differ from WHERE when filtering results?
-
AActs like a WHERE clause but is used for groups rather than rows.
-
BActs like a WHERE clause but is used for rows rather than columns.
-
CActs like a WHERE clause but is used for columns rather than groups.
-
DActs EXACTLY like a WHERE clause.
-
ESorts groups after aggregation is computed.
Answer
Correct Answer: Acts like a WHERE clause but is used for groups rather than rows.
Explanation
Introduction / Context:Aggregations with GROUP BY produce group-level rows. Filtering can occur before grouping (row-level) or after grouping (group-level). Correct placement of predicates determines whether they apply to individual rows or to aggregated results.
Given Data / Assumptions:
- We are using GROUP BY and aggregate functions such as COUNT, SUM, AVG.
- We may need to filter groups based on aggregated values.
- We distinguish row-level filters from group-level filters.
Concept / Approach:WHERE filters individual rows before grouping and cannot reference aggregate results. HAVING filters after GROUP BY and may reference aggregates, thereby including or excluding entire groups based on conditions like COUNT() > 10 or SUM(amount) > 1000.
Step-by-Step Solution:
Decide whether the predicate depends on aggregated values.If yes, place it in HAVING; if not, place it in WHERE.Example: SELECT dept, COUNT() FROM emp GROUP BY dept HAVING COUNT(*) > 5;Verification / Alternative check:Attempting to use aggregates in WHERE yields an error; moving the condition to HAVING resolves it.
Why Other Options Are Wrong:
- B/C: Misstate scope; WHERE is not for columns nor does it act after grouping.
- D: HAVING and WHERE are not identical in timing or allowable expressions.
- E: Sorting is handled by ORDER BY, not HAVING.
Common Pitfalls:Using HAVING for conditions that could be in WHERE, which can hurt performance by grouping unnecessary rows. Prefer WHERE when possible.
Final Answer:Acts like a WHERE clause but is used for groups rather than rows.