Transact SQL – Group By with Rollup

Background

Still working on Utilization reports and needing to build a single report that has the flexibility to support different aggregation levels.

I know I can do so from my reporting tool, but wanted to explore how to I can accomplish same from the SQL Server Engine.

Test Environment

Let us use the Adventure works database:

Detail


use [AdventureWorks2014]
go

declare @separator varchar(10)
declare @zzz varchar(10)
declare @unknown varchar(30)

set @separator = ', '
set @zzz = 'zzz'
set @unknown = 'Unknown'

/*
    List detail lines
*/
select
          [TerritoryID] = tblSP.TerritoryID
        , [CountryRegionCode] = tblST.[CountryRegionCode]
        , [Territory] = tblST.[Name] + @separator + [CountryRegionCode]
        , [fullname] = tblP.LastName + @separator + tblP.FirstName
        , tblSP.[SalesYTD]

from  [Person].[Person] tblP

        inner join [Sales].[SalesPerson] tblSP

            on tblP.BusinessEntityID = tblSP.BusinessEntityID

        left outer join [Sales].[SalesTerritory] tblST

                on tblSP.TerritoryID = tblST.TerritoryID

order by
          [CountryRegionCode]
        , tblST.[Name] + @separator + [CountryRegionCode]
        , tblP.LastName + @separator + tblP.FirstName

detail

Group By with Rollup


use [AdventureWorks2014]
go

declare @separator varchar(10)
declare @zzz varchar(10)
declare @unknown varchar(30)

set @separator = ', '
set @zzz = 'zzz'
set @unknown = ''

/*
    List group by Rollup
*/
;with cteDetail
as
(

   select
              tblP.BusinessEntityID
            , [CountryRegionCode] = isNull(cast(tblST.[CountryRegionCode] as varchar(30)), @unknown)
            , [SalesTerritory] = tblST.[Name]
            , [SalesYTD] = isNull(tblST.[SalesYTD], 0)

   from  [Person].[Person] tblP

            inner join [Sales].[SalesPerson] tblSP

                on tblP.BusinessEntityID = tblSP.BusinessEntityID

            left outer join [Sales].[SalesTerritory] tblST

                    on tblSP.TerritoryID = tblST.TerritoryID

)

, cteGroupRollup
as
(

    select
             case
                        when (GROUPING([CountryRegionCode]) = 1) then 'Region'
                        when (GROUPING([SalesTerritory]) = 1) then 'Sales Territory'
                        when (GROUPING([SalesTerritory]) = 0) then 'Detail'
                        else '' -- for detail line
                end as [groupingHighest]  

            , case
                when (GROUPING([CountryRegionCode]) = 1) then 2
                when (GROUPING([SalesTerritory])  = 1) then 1
                when (GROUPING([SalesTerritory]) = 0) then 0
                else 3
              end as [groupingHighestAsNumeric]  

            , [CountryRegionCode]

            , [Territory] = [SalesTerritory] 

            , [SalesYTD] = sum(isNull([SalesYTD], 0) )

    from  cteDetail cteD

    group by

                    cteD.[CountryRegionCode]

                  , cteD.[SalesTerritory] 

                      with rollup

 )

select 

          [CountryRegionCode] = cteGR.[CountryRegionCode]
        , [Territory] = cteGR.[Territory]
        , [Level] = cteGR.[groupingHighest]
        , [LevelID] = cteGR.groupingHighestAsNumeric
        , cteGR.SalesYTD

from   cteGroupRollup cteGR

order by

            isNull(cteGR.[CountryRegionCode], @zzz)
          , isNull(cteGR.[Territory], @zzz)
          , cteGR.groupingHighestAsNumeric  

GroupByWithRollup

 Explanation:

  1. Where did the first two rows with SalesYTD=0 come from?
    • Honestly struggled with this mightily
    • Found out that they are due to entries in our detail records where Territory ID is null
  2. I really like the fact that the Group By \ Rollup allows record by record visibility into each’s row hierarchy via the Grouping clause
    • It is very, very useful for debugging
    • Also, one can use it to filter based on where in the Hierarchy a record belongs
  3. Quite often one might need the order by \ isNull([data], ‘zzzzz’) to properly order things
    • As the summary records have nulls, and nulls are smaller that comparable numbers, the summary row will usually precede the actual data that is being amalgamated if we yield the isNull \ ‘zzzz’

Listening

I was checking a friend’s YouTube library today and found this music.

It has been out for far too long (for me to just now discover it), but like that pass me down used car, it is new to me

Destiny’s Child – Bad Habit
https://www.youtube.com/watch?v=8H38A4VeKY0

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s