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
union
select N'0 ( 07-12 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 N'0 ( 12 < 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()) > 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
Best Answer
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 )
, thegrp = 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 = 3For monthly records,
grp_2 = LAC
For
TOTAL
,grp_2 = 0
And so, we are ordering by
ORDER BY a.grp,a.grp_2
output:
dbfiddle