BenchMark Quizzes by MDFT Pro

Choose correct WHERE clause to filter the groups

You work for MDFT Pro, where you’re analyzing course purchase data using a Power BI semantic model backed by a Fabric warehouse named DW1. The warehouse contains the following tables and columns that store course purchases:

Table name	Column name
Purchase	CourseID
Purchase	ModifiedDate
Purchase	OrderQty
Course	CourseID
Course	Name

You’ve written the following T-SQL query to summarize OrderQty by year and course, including yearly totals and a grand total:

SELECT 
    YEAR(p.ModifiedDate) AS OrderDate,
    c.Name AS CourseName,
    SUM(p.OrderQty) AS OrderQty
FROM [Purchase] p
INNER JOIN [Course] c
ON c.CourseID = p.CourseID
GROUP BY ROLLUP(YEAR(p.ModifiedDate), c.Name)
ORDER BY OrderDate

You want to exclude only the grand total row, while still returning:

One row per course per year
One row with the subtotal per year for all courses

Which WHERE clause should you add to achieve this?

Choose the correct answer from the options below.

Explanations for each answer:

WHERE GROUPING(c.Name) = 0 is incorrect. This condition keeps only rows where c.Name is not grouped (0) and removes rows where c.Name is grouped (1). This would remove the yearly subtotals and the grand total.
WHERE GROUPING_ID(YEAR(p.ModifiedDate), c.Name) = 0 is incorrect. This condition keeps only the detailed rows (grouping ID 0) where neither column is grouped. It removes the yearly subtotals (grouping ID 1) and the grand total (grouping ID 3).
WHERE GROUPING_ID(YEAR(p.ModifiedDate), c.Name) = 1 is incorrect. This condition keeps only the yearly subtotals (grouping ID 1) where c.Name is grouped. It removes the detailed rows (grouping ID 0) and the grand total (grouping ID 3).
WHERE GROUPING_ID(YEAR(p.ModifiedDate), c.Name) <> 2 is incorrect. Grouping ID 2 refers to rows where YEAR is grouped. This is not a grouping produced by ROLLUP, and so nothing gets removed. The query still returns everything: the detailed rows (grouping ID 0), the yearly subtotals (grouping ID 1), and the grand total (grouping ID 3).
WHERE GROUPING_ID(YEAR(p.ModifiedDate), c.Name) <> 3 is correct.

Learn more about the GROUPING_ID function in SQL Server:

GROUPING_ID

Exam Preparation Quiz

Choose correct WHERE clause to filter the groups