Grouping a SQL Pivot Table and adding a total column

ajcbutler2024 

Add a SELECT on outside of pivot query, treat pivot table as subquery, remove total column from pivot. Now you can group it, and add total column. Like this:

SELECT 
	 S."Request_Display_ID"
	,SUM(S."Open") AS "Open"
	,SUM(S."Awaiting_User_Info") AS "Awaiting_User_Info"
	,SUM(S."Resolved") AS "Resolved"
	,SUM(S."Cancelled") AS "Cancelled"
	,SUM(S."On_Hold") AS "On_Hold"
	,SUM(S."Closed") AS "Closed"
	,SUM(S."Pending_Approval") AS "Pending_Approval"
	,SUM(S."Approval_Not_Received") AS "Approval_Not_Received"
	,SUM(S."Open" + S."Awaiting_User_Info" + S."Resolved" + S."Cancelled" + S."On_Hold" + S."Closed" + S."Pending_Approval" + S."Approval_Not_Received") AS "Total"
FROM
(
	SELECT
	"Pvt"."Request Display ID" as "Request_Display_ID",
	"Pvt"."Open" as "Open",
	"Pvt"."Awaiting User Info" as "Awaiting_User_Info",
	"Pvt"."Resolved" as "Resolved",
	"Pvt"."Cancelled" as "Cancelled",
	"Pvt"."On Hold" as "On_Hold",
	"Pvt"."Closed" as "Closed",
	"Pvt"."Pending Approval" as "Pending_Approval",
	"Pvt"."Approval Not Received" as "Approval_Not_Received",
	"Pvt"."Open" + "Pvt"."Awaiting User Info" + "Pvt"."Resolved" + "Pvt"."Cancelled" + "Pvt"."On Hold" + "Pvt"."Closed" + "Pvt"."Pending Approval" + "Pvt"."Approval Not Received" as Total
	FROM ( SELECT
	"Request Display ID",
	"Status",
	"Timespent in Minutes",
	"TimeSpent_Hours"
	FROM "#StatusHistory_PivotData"
	) AS SD
	PIVOT
	(SUM("SD"."TimeSpent_Hours") FOR "SD"."Status" in ( "Open" , "Awaiting User Info" , "Resolved" , "Cancelled" , "On Hold" , "Closed" , "Pending Approval" , "Approval Not Received" )
	) AS Pvt
)AS S
GROUP BY
	S."Request_Display_ID"
ORDER BY
	S."Request_Display_ID"