Click here to Skip to main content
12,348,536 members (68,161 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

12.2K views
1 bookmarked
Posted

Sorting 'Total' After Data Values

, 1 Jun 2016 CPOL
Rate this:
Please Sign up or sign in to vote.
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
UNION ALL
  SELECT Tier0,Tier1,N'total',COUNT(*) Total FROM [dbo].[TierPark] GROUP BY Tier0,Tier1
UNION ALL
  SELECT Tier0,N'total','',COUNT(*) Total FROM [dbo].[TierPark] GROUP BY Tier0
UNION ALL
  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.

License

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

Share

About the Author

PIEBALDconsult
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

---------------

"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

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

You may also be interested in...

Comments and Discussions

 
QuestionWhy not using Rollup? Pin
Jörgen Andersson2-Jun-16 8:24
protectorJörgen Andersson2-Jun-16 8:24 
GeneralRe: Why not using Rollup? Pin
PIEBALDconsult2-Jun-16 12:39
protectorPIEBALDconsult2-Jun-16 12:39 
GeneralRe: Why not using Rollup? Pin
Jörgen Andersson2-Jun-16 18:21
protectorJörgen Andersson2-Jun-16 18:21 
Questionmy vote of 5 Pin
Auslandskrankenversicherung28-Mar-12 23:05
memberAuslandskrankenversicherung28-Mar-12 23:05 
AnswerRe: my vote of 5 Pin
PIEBALDconsult29-Mar-12 3:22
memberPIEBALDconsult29-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.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.160621.1 | Last Updated 1 Jun 2016
Article Copyright 2012 by PIEBALDconsult
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid