[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

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.
0
zimmer9
Asked:
zimmer9
  • 3
1 Solution
 
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
 
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:
^^^ very nice
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now