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

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.
zimmer9Asked:
Who is Participating?
 
lwadwellCommented:
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
         END
       , CASE WHEN (GROUPING(OffNumber) = 1) THEN char(254)
              ELSE td.MthTitle
         END
       , AcctValue desc

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Normally you would use ROLLUP, but not if you don't want to aggregate by OffNumber
http://msdn.microsoft.com/en-us/library/ms189305(v=sql.90).aspx

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.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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)
VALUES 
	('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

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
^^^ very nice
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.