Sorting 'Total' After Data Values

PIEBALDconsult

5.00/5 (3 votes)

1 Jun 2016CPOL

21.7K

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):

SQL

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:

SQL

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:

SQL

  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)