RKL eSolutions Blog Trends and Insights

Summarizing SQL data using ROLLUP and CUBE

022515_1935_Summarizing1.jpg

Recently, I was asked to assist a client with some complex Transact-SQL (T-SQL) tasks they were using to gather some sales data, group the data, summarize it, by salespersons, SKUs, and so forth. In their present mode they were using a lot of "plumbing" to get this all done—including multiple stored procedures, tables and views.

I suggested to the client that there might be an easier way, now that they are SQL Server 2008 or later.

The raw data set returned by a T-SQL query using the WITH ROLLUP command can be seen in the accompanying figure. The advantage of the WITH ROLLUP command is that the query automatically generates aggregates (sums) for the hierarchy of columns in the query's GROUP BY clause.

Here we see the quantity shipped (QtyShipped) and invoice extended amounts (ExtAmt) summarized by ItemClassID within the Salesperson ID (SperID).

Row 5, for example, shows sales for 'Alfred' for products in the 'Hardware' Item Class. Rows 6 and 7 are also for 'Alfred,' but for Item Class IDs 'Service' and 'Software,' respectively. Row 8, however, is the sum of sales values for 'Alfred' for 'ALL Item Classes.' Beyond that, you can see numbers for 'Jeff,' 'John' and 'Mary' with their associated summed values.

This is very helpful, don't you think?

Here is the T-SQL code that generated this data set, and other data sets we will discuss in this article:

/******************************************************************************

    Summarizing Data Using ROLLUP and CUBE in SQL Server 2008 and Later

*******************************************************************************

ROLLUP generates a result set that shows aggregates for a hierarchy of values

    in the selected columns

CUBE generates a result set that shows aggregates for ALL COMBINATIONS of

    values in the selected columns

*******************************************************************************/

set
nocount
on

 

declare @iCompanyID        varchar(3)

declare @iRollupFlag    bit

declare @iCubeFlag        bit

 

set @iCompanyID    =
'SOA'

set @iRollupFlag    = 0

set @iCubeFlag        = 1

 

if @iRollupFlag = 1

begin

    -- WITH ROLLUP

    select i.CompanyID

        ,
case

            when (grouping(sper.SperID)
= 1 )

                then
'ALL Salespersons'

            else
isnull(sper.SperID,'None')

         end                            'SperID'

        ,
case
when (grouping(ItemClassID)
= 1)

                then
'ALL Item Classes'

            else
isnull(ItemClassID,'None')

         end                            'ItemClassID'

        ,
cast(sum(QtyShipped)
as
int)        'QtyShipped'

        ,
cast(sum(d.ExtAmt)
as
dec(15,2))        'ExtAmt'

    from dbo.tarInvoiceLineDist ild with (nolock)

    join dbo.tarInvoiceDetl d with (nolock)

        on ild.InvoiceLineKey = d.InvoiceLineKey

    join dbo.timItem i with (nolock)

        on d.ItemKey = i.ItemKey

    join dbo.timItemClass ic with (nolock)

        on i.ItemClassKey = ic.ItemClassKey

    join dbo.tarInvoice invc with (nolock)

        on d.InvcKey = invc.InvcKey

    left
outer
join dbo.tarSalesperson sper with (nolock)

        on invc.PrimarySperKey = sper.SperKey

    where i.CompanyID = @iCompanyID

    group
by i.CompanyID

        -- ItemClassID within SperID

        , ic.ItemClassID

        , sper.SperID

        with
rollup

end

 

if @iCubeFlag = 1

begin

    -- WITH CUBE

    select i.CompanyID

        ,
case

            when (grouping(sper.SperID)
= 1 )

                then
'ALL Salespersons'

            else
isnull(sper.SperID,'None')

         end                            'SperID'

        ,
case
when (grouping(ItemClassID)
= 1)

                then
'ALL Item Classes'

            else
isnull(ItemClassID,'None')

         end                            'ItemClassID'

        ,
sum(QtyShipped)                    'QtyShipped'

        ,
cast(sum(d.ExtAmt)
as
dec(15,2))        'ExtAmt'

    from dbo.tarInvoiceLineDist ild with (nolock)

    join dbo.tarInvoiceDetl d with (nolock)

        on ild.InvoiceLineKey = d.InvoiceLineKey

    join dbo.timItem i with (nolock)

        on d.ItemKey = i.ItemKey

    join dbo.timItemClass ic with (nolock)

        on i.ItemClassKey = ic.ItemClassKey

    join dbo.tarInvoice invc with (nolock)

        on d.InvcKey = invc.InvcKey

    left
outer
join dbo.tarSalesperson sper with (nolock)

        on invc.PrimarySperKey = sper.SperKey

    where i.CompanyID = @iCompanyID

    group
by i.CompanyID

        -- ItemClassID within SperID

        , ic.ItemClassID

        , sper.SperID

        with
cube

end

 

if @iCubeFlag <> 1 and @iRollupFlag <> 1

begin

    print
'No Rollups or Cubes requested.'

    set
nocount
off

end

 

If you wish to change how the sums are arranged in the result set, you may change the order in the GROUP BY clause. For example, using the following GROUP BY clause:

Changes the result set to look like this:

Note that now, the salesperson are on individual lines within Item Class ID, and the sum (as on line 7, for example) is the sum for all salespersons for Item Class ID being grouped.

Using the 'with CUBE' command

The WITH CUBE command is slightly different from the WITH ROLLUP command in that it automatically generates an aggregate for all combinations of values in the columns found in the GROUP BY clause. Here is an example of a CUBE result set on the same data:

Just like the WITH ROLLUP command, changing the order in the GROUP BY clause will change the order in which the aggregated rows appear in the result set of a WITH CUBE command.

Use in reporting, dashboards or to trigger actions

Once returned, data sets gathered using WITH ROLLUP or WITH CUBE may be used just like any other data set. They can be placed into tables (permanent or temporary), or returned to a calling program, such as Crystal Reports or a program capable of managing business alerts or work flows such as KnowlegeSync or TaskCentre. Reporting or actions may be predicated on the values found in any of the columns in the returned data set.

Tags: SQL Server
RKL Team

Written by RKL Team

Since 2001, RKL eSolutions has helped growing companies maximize their technology resources and investment. Over the years, we have helped hundreds of small and medium sized businesses as their strategic business partner. We specialize in the needs of Entertainment, Software & SaaS, Professional Services, Manufacturing, and Non Profit organizations. Our experienced consultants have a passion for making every facet of your business successful and are intent on building a long-term relationship with every client.