Question

In SQL, when filtering grouped data, what specific scenario requires the use of the HAVING clause where a WHERE clause would be ineffective?

Accepted Answer

In SQL, the specific scenario requiring the use of the HAVING clause where a WHERE clause would be ineffective is when filtering grouped data based on a condition involving an aggregate function. The WHERE clause operates on individual rows *beforeany grouping or aggregation takes place. This means that a WHERE clause can only evaluate conditions on columns that exist in the original, unaggregated rows. It cannot reference aggregate functions such as COUNT, SUM, AVG, MIN, or MAX, because these functions compute a single summary value from a set of rows, and these summary values are not yet determined when the WHERE clause is evaluated. Conversely, the HAVING clause operates *afterthe GROUP BY clause has organized rows into groups and after aggregate functions have been calculated for each group. Therefore, the HAVING clause is specifically designed to filter these groups based on conditions applied to the results of aggregate functions. For example, if you want to find all product categories where the average price of products in that category is greater than $50, you would first use GROUP BY to group products by category, then calculate the AVG(Price) for each category. To filter these grouped results based on the average price, you must use a HAVING clause like HAVING AVG(Price) > 50. A WHERE clause could not be used here because AVG(Price) is an aggregate value that does not exist for individual rows.

Home → All Courses → Engineering and Technology Courses → Google Data Analytics Professional Certificate → Flashcard

In SQL, when filtering grouped data, what specific scenario requires the use of the HAVING clause where a WHERE clause would be ineffective?