How to use the SQL Rollup statement in SQL Server 2005 based on particular table values?

Posted on 2012-08-20
Last Modified: 2012-08-21
I am creating a SQL Statement using SQL Server 2005. How would I rework the following SQL statement to get the following results? As to the values for the field MthF,
that field has values as follows:

when MthTitl = LESS THAN 6 MOS then MthF = 0
when MthTitl = 6 MOS TO 1 YR      then MthF = 6

select MthTitl, DFrC, DToC, DollTitl,
SUM (CtCust), SUM(SumAcct)
from tblRange
group by MthF, MthTitl, DFrC, DToC, DollTitl with ROLLUP
order by MthF, DollTitl Desc

MthTItl                                 DFrC            DToC             DollTitl             CtCust       SumAcct
LESS THAN 6 MOS   12/03/01  12/08/01 WITH  100k              778      647551536.09
LESS THAN 6 MOS   12/03/01  12/08/01 WITH   50k               604                 36345.55
LESS THAN 6 MOS   12/03/01  12/08/01 WITH   25k               463                    4754.47
LESS THAN 6 MOS   12/03/01  12/08/01 WITH   10k               745           4686476.85
LESS THAN 6 MOS   12/03/01  12/08/01 WITH    1k                964         86447685.37
LESS THAN 6 MOS   12/03/01  12/08/01 WITH    0k                142                    7453.56
LESS THAN 6 MOS                                                                                       3,696     738734251.89  
6 MOS TO 1 YR            12/09/01  12/02/01 WITH  100k           448               51536.09
6 MOS TO 1 YR            12/09/01  12/02/01 WITH   50k              114                    345.55
6 MOS TO 1 YR            12/09/01  12/02/01 WITH   25k              775                 56754.47
6 MOS TO 1 YR            12/09/01  12/02/01 WITH   10k              375                4246.85
6 MOS TO 1 YR            12/09/01  12/02/01 WITH    1k               325              11685.37
6 MOS TO 1 YR            12/09/01  12/02/01 WITH    0k               875              45763.56
6 MOS TO 1 YR                                                                                           2,912         170331.89  
GT                                                                                                                      6,608   738904583.78
Question by:zimmer9
    LVL 75

    Assisted Solution

    by:Anthony Perkins
    Unfortunately, the non-ISO compliant syntax in SQL Server 2005 for WITH ROLLUP only support subtotals for all columns in the GROUP BY clause.  It would appear you only want subtotals for 2 out of 4 columns.

    In SQL Server 2008 there is an ISO compliant syntax for WITH ROLLUP that should do it.
    LVL 25

    Accepted Solution

    If you want to remove the sub-total rows for lower group by elements you will need to select them out and use a sub-query.  e.g.

    select isnull(MthTitl,'GT'), DFrC, DToC, DollTitl, CtCust, SumAcct
    from (select MthTitl, DFrC, DToC, DollTitl, SUM (CtCust) as CtCust, SUM(SumAcct) as SumAcct
          from tblRange 
          group by MthTitl, DFrC, DToC, DollTitl with ROLLUP)v
    where MthTitl is null 
       or (DFrC is not null and DToC is not null and DollTitl is not null)
       or (MthTitl is not null and DFrC is null and DToC is null and DollTitl is null)
    order by case when MthTitl = 'LESS THAN 6 MOS' then 0
                  when MthTitl = '6 MOS TO 1 YR'   then 6
             end, DollTitl Desc

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now