11,487,406 members (70,287 online)
Tip/Trick

# Sorting 'Total' after data values

, 28 Mar 2012 CPOL 3.4K 1
 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 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
```

* 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 contrarian

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

"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

"We learn more from our mistakes than we do from getting it right the first time."

My first rule of debugging: "If you get a different error message, you're making progress."

My golden rule of database management: "Do not unto others' databases as you would not have done unto yours."

My general rule of software development: "Design should be top-down, but implementation should be bottom-up."

 First Prev Next
 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: 26-May-15 11:42 Refresh 1