In your select you have ",COALESCE(LEFT(Reference_2, 2), 'Grand Total') AS 'Sum of Amount Reference 2 Type'"

which the result shown gives you "AL" and multiple "AT"'s and you are expecting one...