Click here to Skip to main content
15,884,298 members
Articles / Database Development / SQL Server

Sorting 'Total' After Data Values

Rate me:
Please Sign up or sign in to vote.
5.00/5 (3 votes)
1 Jun 2016CPOL 20.9K   1   5
Interspersing 'Total' rows among summarized data rows in the correct order

On occasion, I have to write a query* to summarize some hierarchical data and I want to have subtotals as well as a grand total. These various total lines should be in their correct logical positions within the output.

Consider the following table (or a similar view of some normalized tables):

CREATE TABLE [dbo].[TierPark]
  [Tier0] [NVARCHAR](8) NOT NULL
, [Tier1] [NVARCHAR](8) NOT NULL
, [Tier2] [NVARCHAR](8) NOT NULL
, [Value] [NVARCHAR](8) NOT NULL 

With some data rows:

INSERT INTO [dbo].[TierPark] VALUES ( 'A' , 'A' , 'A', 'AAA0' )
INSERT INTO [dbo].[TierPark] VALUES ( 'A' , 'A' , 'A', 'AAA1' )
INSERT INTO [dbo].[TierPark] VALUES ( 'A' , 'A' , 'B', 'AAB0' )
INSERT INTO [dbo].[TierPark] VALUES ( 'A' , 'B' , 'A', 'ABA0' )
INSERT INTO [dbo].[TierPark] VALUES ( 'A' , 'B' , 'C', 'ABC0' )
INSERT INTO [dbo].[TierPark] VALUES ( 'A' , 'B' , 'C', 'ABC1' )
INSERT INTO [dbo].[TierPark] VALUES ( 'Z' , 'Y' , 'Y', 'ZYY0' )
INSERT INTO [dbo].[TierPark] VALUES ( 'Z' , 'Z' , 'Y', 'ZZY1' )
INSERT INTO [dbo].[TierPark] VALUES ( 'Z' , 'Z' , 'Y', 'ZZY2' )
INSERT INTO [dbo].[TierPark] VALUES ( 'Z' , 'Z' , 'Y', 'ZZY3' )
INSERT INTO [dbo].[TierPark] VALUES ( 'Z' , 'Z' , 'Z', 'ZZZ0' )
INSERT INTO [dbo].[TierPark] VALUES ( 'Z' , 'Z' , 'Z', 'ZZZ1' )

The desired output is something like this:

Tier0   Tier1   Tier2   Total
A       A       A       2
A       A       B       1
A       A       total   3
A       B       A       1
A       B       C       2
A       B       total   3
A       total           6
Z       Y       Y       1
Z       Y       total   1
Z       Z       Y       3
Z       Z       Z       2
Z       Z       total   5
Z       total           6
total                   12

but if I'm not careful, I get:

Tier0   Tier1   Tier2   Total
A       A       A       2
A       A       B       1
A       A       Total   3
A       B       A       1
A       B       C       2
A       B       Total   3
A       Total           6
Total                   12
Z       Total           6
Z       Y       Total   1
Z       Y       Y       1
Z       Z       Total   5
Z       Z       Y       3
Z       Z       Z       2

Obviously, this is because the word "Total", starting with the letter "T", sorts before the values Y and Z. What I needed to find was a character that looks like a "T", but which would sort after "Z". It didn't take long to find the Greek letter Tau (t) (Unicode U+03A4) which looks enough like the Latin letter T (depending on your choice of typeface -- I hope it looks OK in the example above, it does in the preview).

The query I used is:

  SELECT Tier0,Tier1,Tier2,COUNT(*) Total FROM [dbo].[TierPark] GROUP BY Tier0,Tier1,Tier2
  SELECT Tier0,Tier1,N'total',COUNT(*) Total FROM [dbo].[TierPark] GROUP BY Tier0,Tier1
  SELECT Tier0,N'total','',COUNT(*) Total FROM [dbo].[TierPark] GROUP BY Tier0
  SELECT N'total','','',COUNT(*) Total FROM [dbo].[TierPark]
ORDER BY Tier0,Tier1,Tier2

Edit: Also consider SELECT NCHAR(0x03A4) + N'otal' as a more obvious technique.

* There are situations in which the only option is a single query, not multiple queries or a report engine.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Software Developer (Senior)
United States United States
BSCS 1992 Wentworth Institute of Technology

Originally from the Boston (MA) area. Lived in SoCal for a while. Now in the Phoenix (AZ) area.

OpenVMS enthusiast, ISO 8601 evangelist, photographer, opinionated SOB, acknowledged pedant and contrarian


"I would be looking for better tekkies, too. Yours are broken." -- Paul Pedant

"Using fewer technologies is better than using more." -- Rico Mariani

"Good code is its own best documentation. As you’re about to add a comment, ask yourself, ‘How can I improve the code so that this comment isn’t needed?’" -- Steve McConnell

"Every time you write a comment, you should grimace and feel the failure of your ability of expression." -- Unknown

"If you need help knowing what to think, let me know and I'll tell you." -- Jeffrey Snover [MSFT]

"Typing is no substitute for thinking." -- R.W. Hamming

"I find it appalling that you can become a programmer with less training than it takes to become a plumber." -- Bjarne Stroustrup

ZagNut’s Law: Arrogance is inversely proportional to ability.

"Well blow me sideways with a plastic marionette. I've just learned something new - and if I could award you a 100 for that post I would. Way to go you keyboard lovegod you." -- Pete O'Hanlon

"linq'ish" sounds like "inept" in German -- Andreas Gieriet

"Things would be different if I ran the zoo." -- Dr. Seuss

"Wrong is evil, and it must be defeated." –- Jeff Ello

"A good designer must rely on experience, on precise, logical thinking, and on pedantic exactness." -- Nigel Shaw

“It’s always easier to do it the hard way.” -- Blackhart

“If Unix wasn’t so bad that you can’t give it away, Bill Gates would never have succeeded in selling Windows.” -- Blackhart

"Use vertical and horizontal whitespace generously. Generally, all binary operators except '.' and '->' should be separated from their operands by blanks."

"Omit needless local variables." -- Strunk... had he taught programming

Comments and Discussions

QuestionWhy not using Rollup? Pin
Jörgen Andersson2-Jun-16 8:24
professionalJörgen Andersson2-Jun-16 8:24 
GeneralRe: Why not using Rollup? Pin
PIEBALDconsult2-Jun-16 12:39
mvePIEBALDconsult2-Jun-16 12:39 
GeneralRe: Why not using Rollup? Pin
Jörgen Andersson2-Jun-16 18:21
professionalJörgen Andersson2-Jun-16 18:21 
Questionmy vote of 5 Pin
Auslandskrankenversicherung28-Mar-12 23:05
Auslandskrankenversicherung28-Mar-12 23:05 
AnswerRe: my vote of 5 Pin
PIEBALDconsult29-Mar-12 3:22
mvePIEBALDconsult29-Mar-12 3:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.