SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query output as per request

chandra_1986Feb 9 2021

HI Experts,
Below is the query used:-, oracle version: 12.1.0.2.0
SELECT TO_CHAR(LOGINLOGOUTTIME,'mm/dd/yyyy:hh24') AS LOGIN_LOGOUT_TIME, ROLE, COUNT( DISTINCT uid ) as UID_COUNT
FROM AT_AGENT WHERE LOGINLOGOUTTIME >= TRUNC(SYSDATE)
AND ROLE IN ('IEFS_SE_TECH','IEFS_SW_TECH','IEFS_MW_TECH','IEFS_W_TECH','TFS_SW_TECH''TFS_SE_TECH',
'TFS_W_TECH','TFS_MW_TECH','DTV_AFC','DTV_HSP','DTV_TECH','BS_MW_TECH','BS_SE_TECH','BS_SW_TECH','BS_W_TECH')
GROUP BY ROLE, TO_CHAR(LOGINLOGOUTTIME,'mm/dd/yyyy:hh24')
ORDER by LOGIN_LOGOUT_TIME desc

output we are getting like:
02/09/2021:04 DTV_AFC 2
02/09/2021:04 DTV_HSP 8
02/09/2021:04 DTV_TECH 3
02/09/2021:04 IEFS_MW_TECH 3
02/09/2021:04 IEFS_SE_TECH 14
02/09/2021:04 IEFS_SW_TECH 7

we need the output like:

Login_logout_time ROLE UID_COUNT
02/09/2021:04 DTV_AFC,DTV_HSP,DTV_TECH,IEFS_MW_TECH,IEFS_SE_TECH,IEFS_SW_TECH 37

This post has been answered by RogerT on Feb 10 2021

Jump to Answer