Forum Discussion
Grouping a SQL Pivot Table and adding a total column
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
Could you supply some sample data?
rodgerkong Here you go. Thanks Rodger
- 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.
