Home » Microsoft » MB6-886 » Which action should you take?
You are a database developer on an instance of SQL Server 2008. Your Transaction table has a PRIMARY KEY constraint defined on the TrxID column. Your
Transaction table also contains columns named TrxType and TrxDisposition.
You want to generate a list of the number of transactions by type and disposition. You want the result set to display the following data:
• A total transaction count for each type
• A total transaction count for each disposition
• A grand total count of all transactions
Which action should you take?
A. Include only the TrxID column in the GROUP BY clause and specify a grouping set that includes only an empty set.
B. Include only the TrxType column in your GROUP BY clause and create a grouping set that includes the TrxType column.
C. Include the TrxType and TrxDisposition columns in your GROUP BY clause and include the WITH ROLLUP option.
D. Include the TrxType and TrxDisposition columns in your GROUP BY clause and include the WITH CUBE option.
Correct Answer: D
Explanation/Reference:
The WITH CUBE clause is used with a GROUP BY clause to return rows that contain summary information. When you specify WITH CUBE, a summary row is included in the result set for each possible combination of the columns specified in the GROUP BY clause. For example, assume you executed the following statement:
SELECT TrxType, TrxDisposition, COUNT(TrxID) AS TrxCount FROM dbo.Transaction GROUP BY TrxType, TrxDisposition WITH CUBE;
The summary rows that represent totals for each transaction type would have a transaction type in the TrxType column, and a NULL value in the TrxDisposition column. The summary rows that represent totals for each disposition would have a disposition in the TrxDisposition column, and a NULL value in the TrxType column. The grand total summary rows would have a NULL value for both the TrxType and TrxDisposition columns.
You should not include only the TrxID column in the GROUP BY clause and specify a grouping set that includes only an empty set, nor include only the TrxType column in your GROUP BY clause and create a grouping set that includes the TrxType column. Neither of these approaches would generate the desired result. You can use grouping sets to explicitly specify the groups for which aggregate information should displayed. Grouping sets are specified by including the GROUPING SETS clause with the GROUP BY clause. The GROUPING SETS clause allows you to explicitly specify the groups for which aggregate information should be displayed. This allows you to use more than one grouping within a single query. The syntax of theGROUP BY clause with a GROUPING SETS clause is as follows:
GROUP BY GROUPING SETS (groupingset1 [,…groupingsetn])
Each grouping set specified can contain one or more columns or an empty set, specified as (). Aggregate rows are returned in the result set for only the specified groups. Specifying an empty set indicates that a grand total row should also be returned in the result set.
You should not include the TrxType and TrxDisposition columns in your GROUP BY clause and include the WITH ROLLUP option. Including the WITH ROLLUP clause would display the total number of transactions of each type and grand totals, but not the number of transactions for each disposition.
