Which query correctly returns ProductID, ProductName, and TotalAmount (SUM(Amount)) for year 2023 with TotalAmount > 10,000 from Staging.Sales?

Which query correctly returns ProductID, ProductName, and TotalAmount (SUM(Amount)) for year 2023 with TotalAmount > 10,000 from Staging.Sales?

• A. SELECT ProductID, ProductName, SUM(Amount) AS TotalAmount FROM Staging.Sales GROUP BY ProductID, ProductName HAVING SUM(Amount) > 10000
• B. SELECT ProductID, ProductName, SUM(Amount) AS TotalAmount FROM Staging.Sales WHERE DATEPART(YEAR, SalesDate) = 2023 GROUP BY ProductID, ProductName HAVING SUM(Amount) > 10000
• C. SELECT ProductID, ProductName, SUM(Amount) AS TotalAmount FROM Staging.Sales WHERE DATEPART(YEAR, SalesDate) = '2023' GROUP BY ProductID, ProductName HAVING SUM(Amount) > 10000
• D. SELECT ProductID, ProductName, SUM(Amount) AS TotalAmount FROM Staging.Sales WHERE DATEPART(YEAR, SalesDate) = 2023 GROUP BY ProductID

Answer: (B)

The idea is to compute a per-product total for a specific year and then keep only those products whose total exceeds a threshold. To do this correctly, you filter the rows by the year first, group by the product identifiers, and apply a post-aggregation filter on the summed amount. This query does that: it uses WHERE with DATEPART(YEAR, SalesDate) = 2023 to limit rows to the year 2023, groups by both ProductID and ProductName so each product yields a single row, computes SUM(Amount) as TotalAmount, and then uses HAVING SUM(Amount) > 10000 to keep only products whose total amount is above 10,000. Why the other approaches don’t fit as well: omitting the year filter would return data from all years, not just 2023; using '2023' as a string with DATEPART can work in some engines but is not robust or clear and risks type-mismatch issues; grouping by only one column while selecting another non-aggregated column is invalid in standard SQL; and some variants mix filters in the aggregate stage or omit the correct pre-aggregation filtering. The shown approach cleanly applies the year filter, correct grouping, and proper post-aggregation filtering.

The idea is to compute a per-product total for a specific year and then keep only those products whose total exceeds a threshold. To do this correctly, you filter the rows by the year first, group by the product identifiers, and apply a post-aggregation filter on the summed amount.

This query does that: it uses WHERE with DATEPART(YEAR, SalesDate) = 2023 to limit rows to the year 2023, groups by both ProductID and ProductName so each product yields a single row, computes SUM(Amount) as TotalAmount, and then uses HAVING SUM(Amount) > 10000 to keep only products whose total amount is above 10,000.

Why the other approaches don’t fit as well: omitting the year filter would return data from all years, not just 2023; using '2023' as a string with DATEPART can work in some engines but is not robust or clear and risks type-mismatch issues; grouping by only one column while selecting another non-aggregated column is invalid in standard SQL; and some variants mix filters in the aggregate stage or omit the correct pre-aggregation filtering. The shown approach cleanly applies the year filter, correct grouping, and proper post-aggregation filtering.