12,633,764 members (30,598 online)
Tip/Trick
alternative version

13.1K views
1 bookmarked
Posted

# Sorting 'Total' After Data Values

, 1 Jun 2016 CPOL
 Rate this:
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.

## Share

 Software Developer (Senior) 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...

 Pro Pro

 First Prev Next
 Re: Why not using Rollup? PIEBALDconsult2-Jun-16 13:39 PIEBALDconsult 2-Jun-16 13:39
 Re: Why not using Rollup? Jörgen Andersson2-Jun-16 19:21 Jörgen Andersson 2-Jun-16 19:21
 my vote of 5 Auslandskrankenversicherung29-Mar-12 0:05 Auslandskrankenversicherung 29-Mar-12 0:05
 Re: my vote of 5 PIEBALDconsult29-Mar-12 4:22 PIEBALDconsult 29-Mar-12 4:22
 Last Visit: 31-Dec-99 19:00     Last Update: 9-Dec-16 6:19 Refresh 1