Welcome to TSQL Tuesday!  Click the picture on the right for more information.

TSQL is a great tool for aggregating lots of data but what do you do with it once it’s aggregated?  Often the answer is to aggregate it again through subtotals and totals.  Normally this will require a second query or if you want to see the totals “in-line”, a trip to Excel.  But, when using aggregation, you can use the WITH ROLLUP or WITH CUBE operator in the GROUP BY clause to generate totals.

MSDN explains the difference between ROLLUP and CUBE as follows:

• CUBE generates a result set that shows aggregates for all combinations of values in the selected columns. 
•  ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.

 If that seems a little fuzzy don’t worry about it – just run the query below with each operator and note the difference in output. 

This query will run on SQL 2005 or 2008 DBs and shows the number of reads since the last restart.  Normally these operators (ROLLUP & CUBE) are used to present an application with pre-aggregated data for performance reasons.  However in this example it is quite useful for ad hoc I/O performance queries. 

SELECT

      OBJECT_SCHEMA_NAME(S.object_id)     AS SchemaName

      ,OBJECT_NAME(S.object_id)           AS ObjectName

      ,SUM(S.user_seeks + S.user_scans + S.user_lookups)                           AS TotalReads

FROM

      sys.dm_db_index_usage_stats         AS S

      INNER JOIN

      sys.indexes                         AS I

            ON S.object_id = I.object_id

WHERE

      OBJECTPROPERTY(S.object_id,‘IsUserTable’) = 1

      AND I.index_id = S.index_id

      AND S.database_id = DB_ID()

GROUP BY

      OBJECT_SCHEMA_NAME(S.object_id)

      ,OBJECT_NAME(S.object_id)

      –WITH ROLLUP / CUBE  

ORDER BY

      1,2,3

  In Figure1 we see the results from the basic aggregation.

 

 

 

 

Figure 1

Adding WITH ROLLUP produces the following.  Row 1 (NULL NULL) is a grand total.  Note that row 2 gives the sub-total for the Person group and so on.

 

 

 

 

 

Figure 2

Finally WITH CUBE produces all possible subtotals. 

 

 

 

 

 

 

 

 

Figure 3

 These operators aren’t Rocket Science but if they save you acouple minutes every time you’d have to make a trip to Excel then I think they are worth adding to the toolbox.