Question
You have a Fabric warehouse that contains a table named Staging.Sales. Staging.Sales contains the following columns.
| Name | Data type | Nullable |
|---|---|---|
| ProductID | Integer | No |
| ProductName | Varchar(30) | No |
| SalesDate | Datetime2(6) | No |
| WholesalePrice | Decimal(18,2) | Yes |
| Amount | Decimal(18,2) | Yes |
You need to write a T-SQL query that will return data for the year 2023 that displays ProductID and ProductName and has a summarized Amount that is higher than 10,000.
Which query should you use?
- A. SELECT ProductID, ProductName, SUM(Amount) AS TotalAmount FROM Staging.Sales WHERE DATEPART(YEAR, SalesDate) = '2023' GROUP BY ProductID, ProductName HAVING SUM(Amount) > 10000 Correct
- B. SELECT ProductID, ProductName, SUM(Amount) AS TotalAmount FROM Staging.Sales GROUP BY ProductID, ProductName HAVING DATEPART(YEAR, SalesDate) = '2023' AND SUM(Amount) > 10000
- C. SELECT ProductID, ProductName, SUM(Amount) AS TotalAmount FROM Staging.Sales WHERE DATEPART(YEAR, SalesDate) = '2023' AND SUM(Amount) > 10000 GROUP BY ProductID, ProductName
- D. SELECT ProductID, ProductName, SUM(Amount) AS TotalAmount FROM Staging.Sales WHERE DATEPART(YEAR, SalesDate) = '2023' GROUP BY ProductID, ProductName HAVING TotalAmount > 10000
Explanation
The year filter applies to individual rows, so it belongs in the WHERE clause, and the condition on the summed Amount applies to each group, so it belongs in the HAVING clause after GROUP BY. Option B fails because SalesDate is neither grouped nor aggregated, so it cannot be referenced in HAVING. Option C fails because an aggregate such as SUM cannot appear in a WHERE clause. Option D fails because T-SQL does not allow a SELECT column alias like TotalAmount to be referenced in HAVING; you must repeat SUM(Amount).
