Grouping a SQL Pivot Table and adding a total column

[ajcbutler2024]

Hi

I have created a Pivot table in SQL code:

Here is the code:

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

I want to be able to group by Request Display ID so there is only one line per ID and also have a total column at the end but everything I have tried doesn't work.

Can anyone help?

Thanks

Aaron

[#2]

@ajcbutler2024 

Could you supply some sample data?

[#3]

@rodgerkong Here you go.  Thanks Rodger

[#4]

I think I have cracked the group by by adding Group By "Request Display ID" and then summing the fields in the top section of the query. I just can't get a total for each row. It just comes up as blank.

[#5]

@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"

[#6]

@rodgerkong Nearly worked. Would have worked. Looks like the application is blocking it.

See attached.

[#7]

@ajcbutler2024 

This application is weird……

Try to use CTE. pivot in CTE body，grouping and calculating total are out there.

Code is here

WITH S
AS
(
	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
)
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
	S
GROUP BY
	S.Request_Display_ID
ORDER BY
	S.Request_Display_ID

[#8]

@ajcbutler2024 

I'm sorry, my friend, I ran in the wrong direction. The problem is not that complicated.

The original SQL you made is OK basicly, except 2 points:

1. The reason that values not group by "Request Display ID" is there is a redundant column in your pivot source, it's "Timespent in Minutes". It prevents pivot from aggregating by "Request Display ID". You can't pivot data from more than one source column.
2. The way you calculate total value was right, but you were ignoring the NULL value, caculating with NULL will get NULL, add a ISNULL to every values be calculated will resolve this.

I fixed your original SQL, please check it out:

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",
	ISNULL("Pvt"."Open", 0) 
		+ ISNULL("Pvt"."Awaiting User Info" , 0)
		+ ISNULL("Pvt"."Resolved" , 0)
		+ ISNULL("Pvt"."Cancelled" , 0)
		+ ISNULL("Pvt"."On Hold", 0)
		+ ISNULL("Pvt"."Closed", 0)
		+ ISNULL("Pvt"."Pending Approval", 0)
		+ ISNULL("Pvt"."Approval Not Received", 0)
		as  "Total"
FROM 
( 
	SELECT
		"Request Display ID",
		"Status",
--		SUM("Timespent in Minutes") AS "Timespent in Minutes",  --Remove this column
		"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
ORDER BY 
	"Pvt"."Request Display ID"