Hello, I'm beginner in plsql query.. and I have a problem.. i don't know how to divide the data into 4 group and give some alias. This data is in one column only.
The said data is in the q.name column. The q.name consist Email Management and IT Equipment, User Group, Switchboard, and each has under-name ( i dont know what is the right term) like in Email Management, there's Email Management: Zimbra, Email Support: SA, etc. I want to put together all data that has Email Management name, IT equipment name.. something like that. I don't want to display the create_time and close time. All I want to display is the q.name, the total count of create and close time of q.name (e.g. let's say the date is 12/12/13 and total of create time is 12 and the close time is 15 so the overall total is 27) it would be display is 27. Please help me.
Thank you
Here's the query:
SELECT 'Data'
||','||q.NAME
||','||COUNT(q.name) --AS NUM_TICKET
||','||TRUNC(t.CREATE_TIME) --AS CNT_CREATE_TIME
||','||TRUNC(t.CLOSE_TIME) --AS CNT_CLOSE_TIME
FROM APP_ACCOUNT.OTRS_QUEUE q
LEFT JOIN APP_ACCOUNT.OTRS_TICKET t
ON q.ID = t.QUEUE_ID
WHERE TRUNC(t.CREATE_TIME) BETWEEN SYSDATE -7 AND TRUNC(SYSDATE) -1
AND TRUNC(t.CLOSE_TIME) BETWEEN SYSDATE -7 AND TRUNC(SYSDATE) -1
GROUP BY q.NAME, q.ID, TRUNC(t.CREATE_TIME), TRUNC(t.CLOSE_TIME)
ORDER BY q.NAME;
My expected output:
Data,Email management,34,02/08/2014
Data, IT Equipment,25,02/08/2014
Data,IT Administration,20,02/08/2014
Some output of the query:
- I want to combine all data has a name of Email Management
Data,E-mail management::Add new SA e-mail (Zimbra account),1,02/08/2014,02/08/2014
Data,E-mail management::Add new SA e-mail (Zimbra account),1,02/10/2014,02/10/2014
Data,E-mail management::Add new SA e-mail (Zimbra account),1,02/11/2014,02/11/2014
Data,E-mail management::Add new SA e-mail (Zimbra account),1,02/12/2014,02/12/2014
Data,E-mail management::Add user e-mail homecredit.ph,1,02/07/2014,02/07/2014
Data,E-mail management::Add user e-mail homecredit.ph,1,02/07/2014,02/10/2014
Data,E-mail management::Add user e-mail homecredit.ph,1,02/09/2014,02/09/2014
Data,E-mail management::Add user e-mail homecredit.ph,1,02/12/2014,02/12/2014
Data,E-mail management::Remove an SA e-mail (Zimbra account),1,02/10/2014,02/10/2014
-Same as above
Data,IT Administration,1,02/10/2014,02/10/2014
Data,IT Administration::Software installation request,1,02/07/2014,02/07/2014
Data,IT Administration::Software installation request,1,02/10/2014,02/10/2014
Data,IT equipment,1,02/07/2014,02/11/2014