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)
SELECT ProductID, ProductName, SUM(Amount) AS TotalAmount FROM Staging.Sales WHERE DATEPART(YEAR, SaleDate) = '2023' GROUP BY ProductID, ProductName HAVING SUM(Amount) > 10000
Summarized Amount by ProductID and ProductName -> Group BY Above 10.000 -> HAVING SUM(Amount) ...
I would say Option D. Sum(Amount) AS TotalAmount is effectively TotalAmount > 10000
The HAVING clause does not allow aliases. This question is testing your knowledge of the order of operations (the order in which SQL statements are processed).
It does though..
It does though..
Where to filter year data Having to filter summerized data Alias like TotalAmount not work in having
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.
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.
SELECT ProductID, ProductName, SUM(Amount) AS TotalAmount FROM Staging.Sales WHERE DATEPART(YEAR, SaleDate) = '2023' GROUP BY ProductID, ProductName HAVING SUM(Amount) > 10000 Answer A is the only answer with valid syntax. Answer B is excluded because the filter on year should be included in the WHERE-clause. It is the result of a function, but it is not an aggregate. Answer C is excluded because the filter on SUM(Amount) should be included in the HAVING-clause, since this is an aggregate. Answer D is excluded because the HAVING-clause does not accept aliases.
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.
It would work in databricks (please correct me if I'm wrong) but not in T-SQL
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.
We want to filter the data to year 2023 only. To filter before an aggregation we use WHERE. So the DATEPART should be inside WHERE. We use HAVING to filter after aggregation, in this case the the summed amount higher than 10000. Therefore, B is incorrect because DATEPART should not be on HAVING. D is incorrect because HAVING cannot take an alias. So, A is the correct answer.
SELECT ProductID, ProductName, SUM(Amount) AS TotalAmount FROM Staging.Sales WHERE DATEPART(YEAR, SaleDate) = '2023' GROUP BY ProductID, ProductName HAVING SUM(Amount) > 10000
SELECT ProductID, ProductName, SUM(Amount) AS TotalAmount FROM Staging.Sales WHERE DATEPART(YEAR, SaleDate) = '2023' GROUP BY ProductID, ProductName HAVING SUM(Amount) > 10000