SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

9 rows affected

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

union

select N'0 ( 07-12 M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet,0 ,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 N'0 ( 12 < M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet ,0,3
	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 ,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

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