How would you sort the records in SQL to show subtotals and totals using SQL Server 2005?

Posted on 2012-08-27
Last Modified: 2012-08-27
I am trying to create a SQL Statement that will yield the following output using SQL Server 2005.

I have a table named tblSummMod with the following fields, types and values:

MthTitle          DollarTitle           OffNumber    AcctValue    
nvarchar          nvarchar              nvarchar       decimal  
----------------   ------------------     ------------     ------------
Less4Months   W100K+Assets     104               $4000.00
Less4Months   W100K+Assets     102               $3000.00
Less4Months   W100K+Assets     107               $5000.00
Less4Months   W100K+Assets     109               $2000.00
Less4Months   W50K+Assets       101               $4000.00
Less4Months   W50K+Assets       103               $7000.00
Less4Months   W50K+Assets       106               $5000.00
Less4Months   W50K+Assets       107               $2000.00
Less4Months   W25K+Assets       101               $8000.00
Less4Months   W25K+Assets       103               $7000.00
Less4Months   W25K+Assets       106               $4000.00
Less4Months   W25K+Assets       107               $1000.00

And I would like to create an output table to export to Excel with the following records
BUT in this particular order of rows because the user wants to see the dollar amounts in DESCENDING ORDER within DollarTitle ranges:

MthTitle          DollarTitle           OffNumber    AcctValue    
nvarchar          nvarchar              nvarchar       decimal  

Less4Months   W100K+Assets     107               $ 5000.00
Less4Months   W100K+Assets     104               $ 4000.00
Less4Months   W100K+Assets     102               $ 3000.00
Less4Months   W100K+Assets     109               $ 2000.00
SUB:                    W100K+Assets                           $14000.00
Less4Months   W50K+Assets       103               $ 7000.00
Less4Months   W50K+Assets       106               $ 5000.00
Less4Months   W50K+Assets       101               $ 4000.00
Less4Months   W50K+Assets       107               $ 2000.00
SUB:                    W50K+Assets                            $18000.00
Less4Months   W25K+Assets       101               $ 8000.00
Less4Months   W25K+Assets       103               $ 7000.00
Less4Months   W25K+Assets       106               $ 4000.00
Less4Months   W25K+Assets       107               $ 1000.00
SUB:                    W25K+Assets                             $20000.00
TOT:                                                                              $52000.00

Even if you need to perform a trick or 2 to get the records in this particular order by adding another column for sorting purposes or concatenating fields.

I could copy the detail records into another table to preserve the detail records.
Then I could perform a SQL insert to get the subtotal records.
And perform a second SQL insert to get the total record.
Question by:zimmer9
    LVL 65

    Expert Comment

    by:Jim Horn
    Normally you would use ROLLUP, but not if you don't want to aggregate by OffNumber

    Any chance you can create an SSRS report with group totals, then export that report to Excel?

    Otherwise, you're asking a lot to maintain the descending sort order within DollarTitle's, and subtotals.
    LVL 65

    Expert Comment

    by:Jim Horn
    Gotta run, but here's a start without the subtotals

    IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
    	DROP TABLE #tmp
    CREATE TABLE #tmp (MthTitle nvarchar(20), DollarTitle nvarchar(20), OffNumber nvarchar(20), AcctValue decimal(19,2))
    INSERT INTO #tmp (MthTitle, DollarTitle, OffNumber, AcctValue)
    	('Less4Months', 'W100K+Assets', '107', 5000.00), 
    	('Less4Months', 'W100K+Assets', '104', 4000.00 ),
    	('Less4Months', 'W100K+Assets', '102', 3000.00), 
    	('Less4Months', 'W100K+Assets', '109', 2000.00), 
    	('Less4Months', 'W50K+Assets', '103', 7000.00 ),
    	('Less4Months', 'W50K+Assets', '106', 5000.00 ),
    	('Less4Months', 'W50K+Assets', '101', 4000.00), 
    	('Less4Months', 'W50K+Assets', '107', 2000.00 ),
    	('Less4Months', 'W25K+Assets', '101', 8000.00 ),
    	('Less4Months', 'W25K+Assets', '103', 7000.00 ),
    	('Less4Months', 'W25K+Assets', '106', 4000.00 ),
    	('Less4Months', 'W25K+Assets', '107', 1000.00 )
    SELECT MthTitle,  DollarTitle, OffNumber, AcctValue, 
    	RANK() OVER (order by DollarTitle) as dollar_title_rank_order,
       RANK() OVER (PARTITION BY DollarTitle ORDER BY AcctValue DESC) as acct_value_rank_order,
    FROM #tmp
    ORDER BY dollar_title_rank_order, acct_value_rank_order

    Open in new window

    LVL 25

    Accepted Solution

    Using a ROLLUP look exactly like the way to go:
    select CASE WHEN (GROUPING(MthTitle) = 1)  THEN 'TOT:'
                WHEN (GROUPING(OffNumber) = 1) THEN 'SUB:'
                ELSE MthTitle
           END AS MthTitle
         , DollarTitle, OffNumber, sum(AcctValue) AcctValue
    from your_table td
    group by MthTitle, DollarTitle, OffNumber with rollup
    having GROUPING(DollarTitle) = 0 or GROUPING(MthTitle) = 1
    order by CASE WHEN (GROUPING(MthTitle) = 1)  THEN char(255)
                  ELSE td.DollarTitle
           , CASE WHEN (GROUPING(OffNumber) = 1) THEN char(254)
                  ELSE td.MthTitle
           , AcctValue desc

    Open in new window

    LVL 65

    Expert Comment

    by:Jim Horn
    ^^^ very nice

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
    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…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    733 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

    17 Experts available now in Live!

    Get 1:1 Help Now