To complete the query that analyzes Sales by ProductName and Year for products with yearly SalesAmount greater than 10000, which clause completes the statement?

To complete the query that analyzes Sales by ProductName and Year for products with yearly SalesAmount greater than 10000, which clause completes the statement?

• A. GROUP BY p.ProductName, d.Year
• B. GROUP BY p.ProductName, d.Year HAVING SUM(s.SalesAmount) > 10000
• C. HAVING SUM(s.SalesAmount) > 10000
• D. GROUP BY p.ProductName, d.Year HAVING SUM(s.SalesAmount)

Answer: (B)

When you want totals per group (here, per product and per year) and then filter those groups by the size of the total, you need to group first and then apply a condition to the aggregated value. Grouping by ProductName and Year makes each group represent one product-year combination. The filter must use a HAVING clause with an aggregate function, because WHERE can’t apply to aggregates. So you’d use a HAVING SUM(s.SalesAmount) > 10000 to keep only the product-year groups whose yearly total exceeds 10,000. The other options either don’t filter the groups, try to use HAVING without a proper GROUP BY, or leave the HAVING clause incomplete.

When you want totals per group (here, per product and per year) and then filter those groups by the size of the total, you need to group first and then apply a condition to the aggregated value. Grouping by ProductName and Year makes each group represent one product-year combination. The filter must use a HAVING clause with an aggregate function, because WHERE can’t apply to aggregates. So you’d use a HAVING SUM(s.SalesAmount) > 10000 to keep only the product-year groups whose yearly total exceeds 10,000. The other options either don’t filter the groups, try to use HAVING without a proper GROUP BY, or leave the HAVING clause incomplete.