Describe core data concepts →mediumMultiple ChoiceObjective-mapped

DP-900 Describe core data concepts Practice Question

Exhibit

Refer to the exhibit.
```sql
SELECT ProductID, SUM(SalesAmount) AS TotalSales
FROM Sales
WHERE OrderDate >= '2024-01-01'
GROUP BY ProductID
HAVING SUM(SalesAmount) > 10000
ORDER BY TotalSales DESC;
```

The exhibit shows a T-SQL query against an Azure SQL Database. What is the purpose of the HAVING clause in this query?

⚠ Common exam trap

Candidates often confuse HAVING with WHERE, mistakenly thinking HAVING filters individual rows before grouping, when in fact WHERE performs that role and HAVING only applies after aggregation.

Answer choices

Why each option matters

Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.

Correct answer & explanation

✓

To filter groups after aggregation

The HAVING clause is used in T-SQL to filter groups after the GROUP BY clause has performed aggregation. In this query, it restricts the result set to only those product categories whose total sales (SUM(Amount)) exceed 1000, which is a condition on the aggregated value, not on individual rows.

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

✗
To sort the result set by TotalSales descending
Why it's wrong here
Sorting the result set by TotalSales descending is the job of the ORDER BY clause, which appears after HAVING in a T-SQL statement. The HAVING clause is evaluated after grouping and aggregation, but it only filters groups; it does not influence the order in which rows are returned. Even if a HAVING predicate is present, you must explicitly specify ORDER BY TotalSales DESC to achieve the requested sort order.
✗
To join two tables
Why it's wrong here
Joining two tables in T-SQL is performed with the JOIN operators (INNER JOIN, LEFT JOIN, RIGHT JOIN, or CROSS JOIN) in the FROM clause, which combine rows from different tables based on a join condition. The HAVING clause is not a join mechanism; it operates on the output of the joined and grouped data, testing conditions against aggregate values. Using HAVING to join tables would be impossible because it has no syntax for specifying table relationships or matching keys.
✓
To filter groups after aggregation
Why this is correct
The HAVING clause is used to filter groups after aggregation has been performed. In the query shown, the GROUP BY clause likely groups rows by one or more columns, and then HAVING applies a condition to the aggregated TotalSales value (e.g., HAVING SUM(TotalSales) > 1000) to keep only certain groups. This differs from WHERE, which cannot reference aggregate functions, whereas HAVING is evaluated after GROUP BY and can directly test SUM, COUNT, AVG, and other aggregate results.
✗
To filter rows before grouping
Why it's wrong here
Filtering rows before grouping is specifically the task of the WHERE clause, which is evaluated immediately after the FROM clause and before any grouping or aggregation occurs. WHERE can test individual column values but cannot reference aggregate functions like SUM(TotalSales) because those aggregates do not exist yet. In contrast, the HAVING clause is processed after GROUP BY, so it works on groups rather than on base rows; attempting to use HAVING for row-level pre-grouping filters is both logically incorrect and syntactically inappropriate in T-SQL.

Go deeper

DP-900 Describe core data concepts Practice Question

Exhibit

Why each option matters

Option-by-option breakdown

Related to this question