This will create a table with total lines. It's not very set-based though, & you end up with the TotalLine column which you need to order it....

WITH Totals AS

(

SELECT

2 as TotalLine,

CAST('' as Varchar(100)) as Work_Id,

CAST('' as Varchar(100)) as Project_name,

Country,

Region,

SUM(Current_Actual_Saving) AS Current_Actual_Saving,

SUM(Current_Forecast) AS Current_Forecast,

SUM(Current_Metric) AS Current_Metric,

SUM(Prior_Actual_Saving) AS Prior_Actual_Saving,

SUM(Prior_Forecast) AS Prior_Forecast,

SUM(Prior_Metric) AS Prior_Metric,

SUM(Prior_2_Actual_Forecast) AS Prior_2_Actual_Forecast,

SUM(Prior_2_Forecst) AS Prior_2_Forecst,

SUM(Prior_2_Metric) AS Prior_2_Metric

FROM My_Table

GROUP BY Country, Region

)

SELECT

1 as TotalLine,

Work_Id,

Project_name,

country,

region,

Current_Actual_Saving,

Current_Forecast,

Current_Metric,

Prior_Actual_Saving,

Prior_Forecast,

Prior_Metric,

Prior_2_Actual_Forecast,

Prior_2_Forecst,

Prior_2_Metric

FROM My_Table

UNION ALL

SELECT *

FROM Totals

ORDER BY Country, Region, TotalLine, Work_Id, Project_Name;