You are a database developer. You plan to design a database solution by using SQL Server 2008. The database includes a table that contains the following product inventory information:
* Department
* Class
* Item
* Quantity
You plan to write a query that produces the sum of quantity data broken into the following groups:
* Department
* Department and Class
* Department and Item
* Department, Class, and Item
You need to write the query by using the minimum possible number of Transact-SQL statements. What should you recommend?
A.
Write a single query that contains a GROUP BY clause.
B.
Write a single query that contains a GROUP BY WITH CUBE clause.
C.
Write a single query that contains a GROUP BY WITH ROLLUP clause.
D.
Write a single query that contains a GROUP BY GROUPING SETS clause.
Explanation:
GROUPING SETS
This clause lets you specify multiple grouping sets in the same query. You can achieve the same functionality in prior versions of SQL Server using multiple queries and using a UNION ALL clause to club the result sets together. With GROUPING SETS though, you can accomplish the same thing with much lesser code and the query will be more efficient as well. This can be very useful for reporting purposes when you want to visualize the data distribution based on different sets of groups.