SQL Server Varchar column with numbers cannot be ordered with aggregate query.

Hi all,

I have an issue when trying to order a varchar column numerically from 1,2,3, etc...  It ends up being unordered no matter what I have tried.  I tried changing the column type to int, number, and BigInt, and still cannot order numerically.  I tried using the following with "Order By": ABS([DAY]), (0 + [DAY]) , CONVERT(), CAST() and keep getting the following error for each:  
"ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator."


Below is a screen shot of the table results (you can see that the "DAY" column is unordered:

My Query:

Select 
a.[DAY],
a.[ASSIST], 
a.[AMERICAN AMR], 
a.[EMS], 
a.[1ST RESPONSE],
a.[HATZOLAH OT],
a.[HATZOLAH BP],
a.[HATZOLAH CA],
a.[HATZOLAH FLATBUSH],
a.[HATZOLAH SG],
a.[HATZOLAH CH],
a.[HIP CENTRAL],
a.[HATZOLAH RO ],
a.[HATZOLAH SI ],
a.[HATZOLAH WI ],
a.[INSTACARE],
a.[KINGSBROOK],
a.[LUTHERAN],
a.[MAIMONIDES],
a.[METROPOLITAN],
a.[MIDWOOD AMBU],
a.[E# MIDWOOD V],
a.[NATION WIDE],
a.[NY PRESBY],
a.[RICHMOND CTY],
a.[SENIOR CARE],
a.[TRANSCARE],
a.[UNITED AMBUL],
a.[VICTORY],
a.[UNKNOWN  ???],

isnull(a.[ASSIST],0) + 
isnull(a.[AMERICAN AMR],0) +
isnull(a.[EMS],0) +
isnull(a.[1ST RESPONSE],0) +
isnull(a.[HATZOLAH OT],0) +
isnull(a.[HATZOLAH BP],0) +
isnull(a.[HATZOLAH CA],0) +
isnull(a.[HATZOLAH FLATBUSH],0) +
isnull(a.[HATZOLAH SG],0) +
isnull(a.[HATZOLAH CH],0) +
isnull(a.[HIP CENTRAL],0) +
isnull(a.[HATZOLAH RO ],0) +
isnull(a.[HATZOLAH SI ],0) +
isnull(a.[HATZOLAH WI ],0) +
isnull(a.[INSTACARE],0) +
isnull(a.[KINGSBROOK],0) +
isnull(a.[LUTHERAN],0) +
isnull(a.[MAIMONIDES],0) +
isnull(a.[METROPOLITAN],0) +
isnull(a.[MIDWOOD AMBU],0) +
isnull(a.[E# MIDWOOD V],0) +
isnull(a.[NATION WIDE],0) +
isnull(a.[NY PRESBY],0) +
isnull(a.[RICHMOND CTY],0) +
isnull(a.[SENIOR CARE],0) +
isnull(a.[TRANSCARE],0) +
isnull(a.[UNITED AMBUL],0) +
isnull(a.[VICTORY],0) +
isnull(a.[UNKNOWN  ???],0) 
Total 
from [AmbDrops].[dbo].[Drops] a

union all
Select 
'Total',
sum(isnull(a.[ASSIST],0)), 
sum(isnull(a.[AMERICAN AMR],0)), 
sum(isnull(a.[EMS],0)), 
sum(isnull(a.[1ST RESPONSE],0)),
sum(isnull(a.[HATZOLAH OT],0)),
sum(isnull(a.[HATZOLAH BP],0)),
sum(isnull(a.[HATZOLAH CA],0)),
sum(isnull(a.[HATZOLAH FLATBUSH],0)),
sum(isnull(a.[HATZOLAH SG],0)),
sum(isnull(a.[HATZOLAH CH],0)),
sum(isnull(a.[HIP CENTRAL],0)),
sum(isnull(a.[HATZOLAH RO ],0)),
sum(isnull(a.[HATZOLAH SI ],0)),
sum(isnull(a.[HATZOLAH WI ],0)),
sum(isnull(a.[INSTACARE],0)),
sum(isnull(a.[KINGSBROOK],0)),
sum(isnull(a.[LUTHERAN],0)),
sum(isnull(a.[MAIMONIDES],0)),
sum(isnull(a.[METROPOLITAN],0)),
sum(isnull(a.[MIDWOOD AMBU],0)),
sum(isnull(a.[E# MIDWOOD V],0)),
sum(isnull(a.[NATION WIDE],0)),
sum(isnull(a.[NY PRESBY],0)),
sum(isnull(a.[RICHMOND CTY],0)),
sum(isnull(a.[SENIOR CARE],0)),
sum(isnull(a.[TRANSCARE],0)),
sum(isnull(a.[UNITED AMBUL],0)),
sum(isnull(a.[VICTORY],0)),
sum(isnull(a.[UNKNOWN  ???],0)),


sum(isnull(a.[ASSIST],0) + 
(isnull(a.[AMERICAN AMR],0)) +  
(isnull(a.[EMS],0)) +  
(isnull(a.[1ST RESPONSE],0)) + 
(isnull(a.[HATZOLAH OT],0)) + 
(isnull(a.[HATZOLAH BP],0)) + 
(isnull(a.[HATZOLAH CA],0)) + 
(isnull(a.[HATZOLAH FLATBUSH],0)) + 
(isnull(a.[HATZOLAH SG],0)) + 
(isnull(a.[HATZOLAH CH],0)) + 
(isnull(a.[HIP CENTRAL],0)) + 
(isnull(a.[HATZOLAH RO ],0)) + 
(isnull(a.[HATZOLAH SI ],0)) + 
(isnull(a.[HATZOLAH WI ],0)) + 
(isnull(a.[INSTACARE],0)) + 
(isnull(a.[KINGSBROOK],0)) + 
(isnull(a.[LUTHERAN],0)) + 
(isnull(a.[MAIMONIDES],0)) + 
(isnull(a.[METROPOLITAN],0)) + 
(isnull(a.[MIDWOOD AMBU],0)) + 
(isnull(a.[E# MIDWOOD V],0)) + 
(isnull(a.[NATION WIDE],0)) + 
(isnull(a.[NY PRESBY],0)) + 
(isnull(a.[RICHMOND CTY],0)) + 
(isnull(a.[SENIOR CARE],0)) + 
(isnull(a.[TRANSCARE],0)) + 
(isnull(a.[UNITED AMBUL],0)) + 
(isnull(a.[VICTORY],0)) + 
(isnull(a.[UNKNOWN  ???],0))
)
from [AmbDrops].[dbo].[Drops] a

order by [DAY]

Select allOpen in new window

Any help would be appreciated.  Thank you!