You are a database developer on an instance of SQL Server 2008. You have a SalesOrderDetail table defined as follows:
You want to display product information for products ordered through special promotional offers. You execute the following statement:
SELECT SpecialOfferID, ProductID, SUM(OrderQty) AS Total
FROM SalesOrderDetail
GROUP BY GROUPING SETS((ProductID), (ProductID, SpecialOfferID), ())
HAVING GROUPING_ID(ProductID) = 0;
When reviewing the result set returned, you notice there are no grand totals displayed.
Which action should you take to display the same result including a grand total of products ordered?
A. Remove the empty grouping set from the GROUPING SETS clause.
B. Remove the HAVING clause from the query.
C. Modify the HAVING clause to check for a value of 1 returned by the GROUPING_ID function.
D. Remove the GROUP BY and HAVING clauses and use GROUP BY SpecialOfferID WITH ROLLUP in the GROUP BY clause.
Correct Answer: B
Explanation/Reference:
In this scenario, you included a 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 the GROUP BY clause with a GROUPING SETS clause is as follows:
GROUP BY GROUPING SETS (groupingset1 [,…groupingsetn])
Each grouping set can contain one or more columns or an empty set. Aggregate rows are returned in the result set for only the specified groups.
Specifying an empty set, with (), indicates that a grand total row should also be returned in the result set. In this statement, you specified an empty set in the GROUPING SETS clause. However, you used the GROUPING_ID function in the HAVING clause. The GROUPING_ID function returns either 0 or a 1 to identify the level of grouping. This HAVING clause suppresses the grand total rows.
You can also use the CUBE and ROLLUP operators to aggregate data. The ROLLUP operator groups the selected rows in the result set based on the values in the GROUP BY clause and returns one row as a summary row for each group. The ROLLUP operator can be used to generate totals and subtotals. Using the ROLLUP operator, a row containing the subtotal and the total is also returned in the result set. 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.
When using ROLLUP, CUBE, or GROUPING SETS, aggregate rows can be identified by the NULL values. Summary rows for grand totals will contain
NULL values for all grouping columns. If you grouped using two columns, such as ProductID and SpecialOfferID in this scenario, you could identify the aggregate rows as follows:
Summary rows that represent totals for eachProductID would have a value for ProductID and a NULL value for SpecialOfferID.
Summary rows that represent totals for eachSpecialOfferID would have a value for SpecialOfferID and a NULL value for ProductID.
You should not remove the empty grouping set from the GROUPING SETS clause. To include grand totals in a query that uses the GROUPING SETS clause, you must include an empty set.
You should not modify the HAVING clause to check for a value of 1 returned by the GROUPING_ID function. This would return only the grand total row, and filter out the aggregate rows for products and the aggregate rows for unique ProductID and SpecialOfferID combinations.
You should not remove the GROUP BY and HAVING clauses and use GROUP BY SpecialOfferID WITH ROLLUP in the GROUP BY clause. All columns in the SELECT list must either use an aggregate function or be included in the query’s GROUP BY clause.
