You work for MDFT Academy, a well known training agency. MDFT Academy operates 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?
Choose all correct answers from the options below.
Please select at least one answer!
Correct!
I'm sorry, your answers are not correct.
Explanations for each answer:
SELECT ProductID, ProductName, SUM(Amount) AS TotalAmount
FROM Staging.Sales
WHERE DATEPART(YEAR, SalesDate) = '2023'
GROUP BY ProductID, ProductName
HAVING SUM(Amount) > 10000
is correct
SELECT ProductID, ProductName, SUM(Amount) AS TotalAmount
FROM Staging.Sales
GROUP BY ProductID, ProductName
HAVING DATEPART(YEAR, SalesDate) = '2023' AND SUM(Amount) > 10000
is incorrect. This query incorrectly uses DATEPART in the HAVING clause instead of the WHERE clause. HAVING should only be used for aggregate conditions.
SELECT ProductID, ProductName, SUM(Amount) AS TotalAmount
FROM Staging.Sales
WHERE DATEPART(YEAR, SalesDate) = '2023' AND SUM(Amount) > 10000
is incorrect. This query incorrectly tries to use SUM() in the WHERE clause. Aggregate functions can only be used in HAVING clause when filtering.
SELECT ProductID, ProductName, SUM(Amount) AS TotalAmount
FROM Staging.Sales
WHERE DATEPART(YEAR, SalesDate) = '2023'
GROUP BY ProductID, ProductName
HAVING SUM(Amount) > 10000
ORDER BY TotalAmount
is correct