Order one column alphabetically while keeping a Total Line Footer at Bottom of Table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Order one column alphabetically while keeping...

Order one column alphabetically while keeping a Total Line Footer at Bottom of Table

Rate Topic

Display Mode

Topic Options

Author

Message

OP_HELP

Posted Thursday, February 10, 2011 7:54 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 17, 2012 11:56 PM
Points: 100, Visits: 334

Hello,

I've written this SQL , running it in Access 2003, which produces the information I need, including a footer showing the Total.

However, I would like to Sort Col1 alphabetically (From A-Z), but so far, has been unsuccessful. Below is the main pseudcode for this:

SELECT “Total“ AS Col1, “ “ AS Col2, “ “ AS Col3, Count([TableA.Column1]) AS [Count_TableA_Column1]
FROM…TableA INNER JOIN TableB INNER JOIN TableC
UNION
SELECT TableB.Col1, TableB.Col2, TableB.Col3, Count([TCPIP Table].[Term #]) AS [CountOfTerm #]
GROUP BY TableB.Col1, TableB.Col2, TableB.Col3
ORDER BY Col1, [Count_TableA_Column1]

Col1 Col2 Col3 CountOfTerm#
A - - 5
D - - 4
Total 11
Z - - 2

When I remove the "Col1" from the "Order by" in the second SELECT, I get the "Total" row pushed to the bottom, but then the Col1 is all unordered like this, but the Total appears at the bottom, as I want it.

Z 2
D 4
A 5
Total 11

Is there a way to order by Column 1 while still getting the Total Row at the bottom? Thanks for any help

Post #1062001

OP_HELP

Posted Thursday, February 10, 2011 8:04 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 17, 2012 11:56 PM
Points: 100, Visits: 334

I've reached a temporary solution. I added a space ( " "+ [TableB.Col1] ) to the second Select statement in the Union query, inserting a space before each cell of data. So, when it sorts, it sorts everything with the Space first, and then the "Total" which has no space before it in the first Select.

Post #1062013

Dwayne Dibley

Posted Thursday, February 10, 2011 8:08 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:41 AM
Points: 365, Visits: 2,900

SELECT “Total“ AS Col1, “ “ AS Col2, “ “ AS Col3, Count([TableA.Column1]) AS [Count_TableA_Column1], 2 as roworder
FROM…TableA INNER JOIN TableB INNER JOIN TableC
UNION
SELECT TableB.Col1, TableB.Col2, TableB.Col3, Count([TCPIP Table].[Term #]) AS [CountOfTerm #],1 as roworder
GROUP BY TableB.Col1, TableB.Col2, TableB.Col3
ORDER BY roworder,Col1, [Count_TableA_Column1]

Post #1062026

« Prev Topic | Next Topic »

Permissions