Microsoft SQL Server 2008 R2 Unleashed- P163

Microsoft SQL Server 2008 R2 Unleashed- P163:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1564 CHAPTER 42 What s New for Transact-SQL in SQL Server 2008 Listing demonstrates how to use the GROUPING SETS operator to perform three groupings on three individual columns in a single query. LISTING GROUPING SETS Example Perform a grouping by type grouping by pub_id and grouping by price SELECT type pub_id price sum isnull ytd_sales 0 AS ytd_sales FROM titles where pub_id 9 GROUP BY GROUPING SETS type pub_id price go type pub_id price ytd_sales NULL NULL NULL 0 NULL NULL 111 NULL NULL 750 NULL NULL 4095 NULL NULL 18972 NULL NULL 14294 NULL NULL 4095 NULL NULL 40968 NULL NULL 3336 NULL NULL 2045 NULL NULL 8780 NULL 0736 NULL 28286 NULL 0877 NULL 44219 NULL 1389 NULL 24941 business NULL NULL 30788 mod_cook NULL NULL 24278 popular_comp NULL NULL 12875 psychology NULL NULL 9939 tradcook NULL NULL 19566 In the output in Listing the first 11 rows are the results grouped by price the next 3 rows are grouped by pub_id and the bottom 5 rows are grouped by type. Now you can modify this query to include a super-aggregate for all rows by adding a null field list as shown in Listing . GROUP BY Clause Enhancements 1565 42 LISTING GROUPING SETS Example with Null Field List to Generate Super-Aggregate SELECT type pub_id price sum isnull ytd_sales 0 AS ytd_sales FROM titles where pub_id 9 GROUP BY GROUPING SETS type pub_id price go type pub_id price ytd_sales NULL NULL NULL 0 NULL NULL 111 NULL NULL 750 NULL NULL 4095 NULL NULL 18972 NULL NULL 14294 NULL NULL 4095 NULL NULL 40968 NULL NULL 3336 NULL NULL 2045 NULL NULL 8780 NULL NULL NULL 97446 NULL 0736 NULL 28286 NULL 0877 NULL 44219 NULL 1389 NULL 24941 business NULL NULL 30788 mod_cook NULL NULL 24278 popular_comp NULL NULL 12875 psychology NULL NULL 9939 tradcook NULL NULL 19566 If you look closely at the results in Listing you see there are .