Order by column which casted to varchar

situation is that, i want to prepare datasets for front end chart. the raw data from table contains

LAC | FLUID_MERIT|FA_BDATE   |GRP |FARM_FK
------------------------------------------
0   |234.56      |2020-01-01 |12  |10048
1   |234.56      |2009-01-01 |13  |10048
10  |234.56      |2020-01-01 |13  |10048
0   |234.56      |2020-01-01 |13  |10049
2   |234.56      |2009-01-01 |12  |10049
3   |234.56      |2009-01-01 |12  |10048
0   |234.56      |2020-01-01 |12  |10048

so to prepare the average dataset i want is group by LAC and beside there is condition for LAC = 0 where it must be separated by month range. the query below is a sample

select N'0 ( 01-06 M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet 
from HerdAnalytics_tbl 
where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE())  6 and DATEDIFF(MONTH, FA_BDATE , GETDATE())  12 

union 

select CAST(LAC as varchar),CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet 
from HerdAnalytics_tbl where GRP = 12 and LAC in (select LAC from HerdAnalytics_tbl 
where FARM_FK = 10048 and LAC != 0)  
group by LAC

union 

select 'TOTAL' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet 
from HerdAnalytics_tbl 
where GRP = 12

when i use union to merge them i could not use order by in the 4th query and also it sort by LAC as a varchar data so lac 10 comes after 1 since it is a varchar as shown below

YDS           | DataSet
------------------------
0 ( 01-06 M ) | 117.78
0 ( 07-12 M ) | 465.26
0 ( 12 < M )  | NULL
1             | 292.58
10            | -62.55
2             | 321.40
3             | 278.24
4             | 308.68
5             | 267.48
6             | 229.36
7             | 165.18
8             | 105.14
9             | 65.68
TOTAL         | 149.95

is there any way to sort properly the 4th query

The Order by clause is the only one that guarantee the order.

But if you really want in back end , then you can add some additional columns/attributes to make it easier.

I added a grp = group level and a subgroup grp_2 =group level 2.

For 0 ( 01-06 M ),0 ( 07-12 M ),0 ( 12 < M ), the grp = 0

For monthly records, grp = 1

For TOTAL, grp = 2

And for grp_2, we have: 0 ( 01-06 M )grp_2 = 1,0 ( 07-12 M ) grp_2 = 2,0 ( 12 < M ) grp_2 = 3

For monthly records, grp_2 = LAC

For TOTAL, grp_2 = 0

And so, we are ordering by ORDER BY a.grp,a.grp_2

SELECT a.YDS,a.DataSet
FROM
(
    select N'0 ( 01-06 M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet ,0 as grp,1 as grp_2
    from HerdAnalytics_tbl 
    where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE())  6 and DATEDIFF(MONTH, FA_BDATE , GETDATE())  12 

    union 

    select CAST(LAC as varchar),CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet ,1,Lac
    from HerdAnalytics_tbl 
    where GRP = 12 
        and LAC in (select LAC from HerdAnalytics_tbl where FARM_FK = 10048 and LAC != 0)  
    group by LAC

    union 

    select 'TOTAL' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet ,2,0
    from HerdAnalytics_tbl 
    where GRP = 12
)as a
ORDER BY a.grp,a.grp_2

output:

YDS             DataSet
0 ( 01-06 M )   NULL
0 ( 07-12 M )   NULL
0 ( 12 < M )    234.56
3               234.56
4               234.56
10              234.56
TOTAL           234.56

dbfiddle

StackExchange Database Administrators Q#285858, answer score: 3