Exam DP-600 topic 1 question 10 discussion

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.

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

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.

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.

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 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.

the table includes 'SalesDate' but every answer includes 'SaleDate' so they are all wrong!!! E. None of the above

IMHO, A

I would say Option D. Sum(Amount) AS TotalAmount is effectively TotalAmount > 10000

SELECT ProductID, ProductName, SUM(Amount) AS TotalAmount FROM Staging.Sales WHERE DATEPART(YEAR, SaleDate) = '2023' GROUP BY ProductID, ProductName HAVING SUM(Amount) > 10000

second one could be correct but saledate is neither used in group by nor in any aggregate function. Hence A is the correct answer.

BTW spark sql allows us to refer to the count() alias

The answer is A, no-brainer.

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).

Where to filter year data Having to filter summerized data Alias like TotalAmount not work in having

TotalAmount can not be used with HAVING. You must use SUM(Amount)