ramlicious Blogs by Tina & Prabhu

September 14, 2010

ROLLUP and CUBE in SQL Server

Filed under: SQL Server — Prabhuram @ 5:18 am

Have you ever thought of these simple clauses called ROLLUP and CUBE when generating reports?

SELECT
      Title,
      City,
      Count(*) [Total]
FROM
      Northwind..Employees
GROUP BY
      Title, City
Order By
      Title, City 

The above query is a commonly used GROUP BY. For the presentation and reporting purpose the same can be presented as


Using

SELECT
      Title,
      City,
      Count(*) [Total]
FROM
      Northwind..Employees
GROUP BY
      Title, City
WITH ROLLUP
Order By
      Title, City  

Note the WITH ROLLUP from the above query. If you wanted to distribute even the total for Title distributed by City use the WITH CUBE like:

SELECT
      Title,
      City,
      Count(*) [Total]
FROM
      Northwind..Employees
GROUP BY
      Title, City
WITH CUBE
Order By
      Title, City 


To remove the annoying NULL and make it meaningful use the GROUPING

SELECT
      CASE WHEN GROUPING(Title) =1 THEN 'All Titles' Else Title End [Title],
      CASE WHEN GROUPING(City) =1 THEN 'All Cities' Else City End [City],
      Count(*) [Total]
FROM
      Northwind..Employees
GROUP BY
      Title, City
WITH CUBE
Order By

      Title, City 

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment

You must be logged in to post a comment.

Powered by WordPress