You have a Fabric warehouse that contains a table named Staging.Sales. Staging.Sales contains the following columns.
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?
To return data for the year 2023 displaying ProductID and ProductName with a summarized Amount higher than 10,000, the query should filter the records by the year first, group the data by ProductID and ProductName, and then use the HAVING clause to filter the groups based on the summarized Amount. The correct query is: SELECT ProductID, ProductName, SUM(Amount) AS TotalAmount FROM Staging.Sales WHERE DATEPART(YEAR, SalesDate) = 2023 GROUP BY ProductID, ProductName HAVING SUM(Amount) > 10000. This query ensures that records are filtered by year, grouped appropriately, and the summarized Amount is correctly compared to 10,000.
Anything without HAVING() + an aggregate is incorrect. HAVING was created for SQL to deal with filtering using an aggregate. Any option that references TotalAmount is incorrect because there is no nested statement in the syntax. Anything that uses HAVING() + DATEPART() is incorrect because you use a where clause for that. The answer is A.
TotalAmount can not be used with HAVING. You must use SUM(Amount)
Summarized Amount by ProductID and ProductName -> Group BY Above 10.000 -> HAVING SUM(Amount) ...
SELECT ProductID, ProductName, SUM(Amount) AS TotalAmount FROM Staging.Sales WHERE DATEPART(YEAR, SaleDate) = '2023' GROUP BY ProductID, ProductName HAVING SUM(Amount) > 10000
I would say Option D. Sum(Amount) AS TotalAmount is effectively TotalAmount > 10000
Where to filter year data Having to filter summerized data Alias like TotalAmount not work in having
BTW spark sql allows us to refer to the count() alias
In some instances, you might want to exclude individual rows from groups (using a WHERE clause) before applying a condition to groups as a whole (using a HAVING clause). A HAVING clause is like a WHERE clause, but applies only to groups as a whole (that is, to the rows in the result set representing groups), whereas the WHERE clause applies to individual rows. A query can contain both a WHERE clause and a HAVING clause. In that case: The WHERE clause is applied first to the individual rows in the tables or table-valued objects in the Diagram pane. Only the rows that meet the conditions in the WHERE clause are grouped. The HAVING clause is then applied to the rows in the result set. Only the groups that meet the HAVING conditions appear in the query output. You can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function.
A. SELECT ProductID, ProductName, SUM(Amount) AS TotalAmount FROM Staging.Sales WHERE DATEPART(YEAR, SaleDate) = '2023' GROUP BY ProductID, ProductName HAVING SUM(Amount) > 10000 Selected data is first filtered by Year, then grouped by ProductID and ProductName to compute the TotalAmount. Finally, only SUM(Amount) cane be used after HAVING (not the alias).
The answer is A, no-brainer.
second one could be correct but saledate is neither used in group by nor in any aggregate function. Hence A is the correct answer.
IMHO, A
the table includes 'SalesDate' but every answer includes 'SaleDate' so they are all wrong!!! E. None of the above
The Table does not have to include SalesDate. It just return data for the year 2023, which can be filtered under the hood and not to be shown in SELECT.
Actually both D & A are correct, aren't they? "TotalAmount" is already available after the GROUP BY clause, or are there any reasons D is not correct?
In D TotalAmount is an alias (HAVING TotalAmount > 10000) and only ORDER BY accepts aliases.
I am confused between A and B. But A is a correct Answer. Here is the explanation- If we use non-aggregate function to Having clause it will throw below error- Column 'SalesDate' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.