Group totals on a report
Hi,
I have the following query but would like to show the total Time Spent and total Amount at the bottom of each group (Site).
SELECT rctd.FIRST_NAME "Technician",
sdo.NAME "Site",wo.WORKORDERID "Request ID",
aau.FIRST_NAME "Requester",
wo.TITLE "Subject",
cd.CATEGORYNAME "Category",
std.STATUSNAME "Request Status",
pd.PRIORITYNAME "Priority",
SUM(rc.MM2COMPLETEREQUEST/1000/3600) "Time Spent",
SUM(rc.AMOUNT) "Amount"
FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID INNER JOIN RequestCharges rc ON wo.WORKORDERID=rc.WORKORDERID LEFT JOIN RCTechnicianIDs rct ON rc.REQUESTCHARGEID=rct.REQUESTCHARGEID LEFT JOIN SDUser rcti ON rct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID WHERE (((wo.COMPLETEDTIME >= <from_thismonth>) AND ((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL))) AND ((wo.COMPLETEDTIME <= <to_thismonth>) AND (((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL)) AND (wo.COMPLETEDTIME != -1)))) AND wot.THD_WOID=wot.WORKORDERID
GROUP BY 1,2,3
ORDER BY 1, 2
I'd really like the output to be similar to a custom report where the report is GROUPED BY, ORDERED BY, then a SUMMARY TYPE is applied in this case SUM of TIME SPENT and AMOUNT.
Any help would be appreciated.
Regards,
Ian
I have the following query but would like to show the total Time Spent and total Amount at the bottom of each group (Site).
SELECT rctd.FIRST_NAME "Technician",
sdo.NAME "Site",wo.WORKORDERID "Request ID",
aau.FIRST_NAME "Requester",
wo.TITLE "Subject",
cd.CATEGORYNAME "Category",
std.STATUSNAME "Request Status",
pd.PRIORITYNAME "Priority",
SUM(rc.MM2COMPLETEREQUEST/1000/3600) "Time Spent",
SUM(rc.AMOUNT) "Amount"
FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID INNER JOIN RequestCharges rc ON wo.WORKORDERID=rc.WORKORDERID LEFT JOIN RCTechnicianIDs rct ON rc.REQUESTCHARGEID=rct.REQUESTCHARGEID LEFT JOIN SDUser rcti ON rct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID WHERE (((wo.COMPLETEDTIME >= <from_thismonth>) AND ((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL))) AND ((wo.COMPLETEDTIME <= <to_thismonth>) AND (((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL)) AND (wo.COMPLETEDTIME != -1)))) AND wot.THD_WOID=wot.WORKORDERID
GROUP BY 1,2,3
ORDER BY 1, 2
I'd really like the output to be similar to a custom report where the report is GROUPED BY, ORDERED BY, then a SUMMARY TYPE is applied in this case SUM of TIME SPENT and AMOUNT.
Any help would be appreciated.
Regards,
Ian