BenchMark Quizzes by MDFT Pro

Choose correct GROUP BY clause(s) for summarizing order quantities

You have a Fabric workspace that contains a 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 need to summarize order quantities as follows:

Summarize order quantities by year and course.
Include the yearly sum of order quantities for all the courses in each row.
Include the grand total for all courses and all years.

You have the following T-SQL query:

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 ________________
ORDER BY OrderDate

How can you complete the T-SQL statement?

Choose the correct answer from the options below.

Explanations for each answer:

CUBE(YEAR(p.ModifiedDate), c.Name) is incorrect. CUBE generates all possible combinations of groupings, including course-level subtotals for all years that are not required by the question.
GROUPING SETS((YEAR(p.ModifiedDate), c.Name), (YEAR(p.ModifiedDate))) is incorrect. GROUPING SETS only creates groupings for year and course combinations, and year totals, but does not include the grand total required by the question.
ROLLUP(YEAR(p.ModifiedDate), c.Name) is correct. ROLLUP creates exactly the groupings needed: year and course combinations, year totals, and the grand total, without any extra subtotals.
YEAR(p.ModifiedDate), c.Name is incorrect. A simple GROUP BY would only show the totals by course and year, without any subtotals or the grand total required by the question.

Learn more about GROUP BY clauses in SQL Server:

SQL Server GROUP BY

Exam Preparation Quiz

Choose correct GROUP BY clause(s) for summarizing order quantities