Query output as per request

RogerT

It would really help if you provided data + structure but:

with at_agent(loginlogouttime, role, "UID") as (select trunc(sysdate) + 1/24, 'IEFS_SE_TECH' , 47 from dual union all
                        select trunc(sysdate) + 1/24, 'IEFS_SW_TECH' , 47 from dual union all
                        select trunc(sysdate) + 1/24, 'IEFS_SW_TECH' , 48 from dual union all
                        select trunc(sysdate) + 2/24, 'TFS_SW_TECH'  , 48 from dual)
select LoginLogoutTime
   ,LISTAGG(ROLE,',') within group (order by role) as RoleList
   ,MAX(UID_COUNT)
 from (select DISTINCT trunc(loginlogouttime,'HH24') as LoginLogoutTime
       ,count(distinct "UID") over (PARTITION BY trunc(loginlogouttime,'HH24')) as UID_COUNT
       ,role
     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 LoginLogoutTime
 order by LoginLogoutTime desc;

should do it. Just remove the with clause, as you have an at_agent table.

Difference to your original post ... if a user (47) is logged in within one our with two different roles, the user get only counted once.

hth

mathguy

https://community.oracle.com/tech/developers/discussion/comment/16784495#Comment_16784495

I suggested already. Did you try it?

chandra_1986

Thanks experts, we need to include:

TO_CHAR(LOGINLOGOUTTIME,'mm/dd/yyyy:hh24') AS LOGIN_LOGOUT_TIME

RogerT

I am pretty sure you can manage that by your own.

chandra_1986

Yes i tried experts,

with :-

 select TO_CHAR(LOGINLOGOUTTIME,'mm/dd/yyyy:hh24') AS LOGIN_LOGOUT_TIME

  ,LISTAGG(ROLE,',') within group (order by ROLE) as RoleList

  ,MAX(UID_COUNT)

 from (select DISTINCT trunc(loginlogouttime,'HH24') as LoginLogoutTime

    ,count(distinct "UID") over (PARTITION BY trunc(loginlogouttime,'HH24')) as UID_COUNT

    ,ROLE

   from at_agent

   where LOGINLOGOUTTIME >= TRUNC(SYSDATE)

   and AGENTROLE 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 TO_CHAR(LOGINLOGOUTTIME,'mm/dd/yyyy:hh24')

 order by LoginLogoutTime desc;

 

 ORA-00979: not a GROUP BY expression

00979. 00000 - "not a GROUP BY expression"

*Cause:   

*Action:

Error at Line: 262 Column: 11

 

 

 ORA-00979: not a GROUP BY expression

00979. 00000 - "not a GROUP BY expression"

*Cause:   

*Action:

Error at Line: 262 Column: 11

RogerT

I am pretty sure line 262 points to your order by clause ...

hth

mathguy

https://community.oracle.com/tech/developers/discussion/comment/16784555#Comment_16784555

I believe you are still modifying your code before you post it.

In your latest reply, in the subquery, you are selecting ROLE. But in the WHERE clause you reference a different name, AGENTROLE. Based on your original question, I assume these are the same thing, not two different columns in the same table. If so, the subquery would throw an error. Is ROLE an alias for AGENTROLE? If it is, then you modified your query before posting it here. I won't be able to help you if you keep doing that.

The error you reported references the ORDER BY condition at the very end. You could order by LOGIN_LOGOUT_TIME which is defined in the SELECT clause. The reason you can't order by LOGINLOGOUTTIME (which is the column from the subquery) is that in the outer query you didn't group by LOGINLOGOUTTIME, you grouped by a function of it. The best solution would be, in the outer query, to group by LOGINLOGOUTTIME (the exact column coming from the subquery), and only use TO_CHAR in the SELECT clause, not in GROUP BY. Then you could leave ORDER BY as is, since LOGINLOGOUTTIME will be a "group by" column.

This will fix the query. But I don't know what your latest query has to do with the original one. In the original, you grouped by ROLE and "hour" and counted rows. In your new version, you do something very different - the subquery doesn't group by role, it just does some strange "distinct" things. I have no clue what the purpose is. My suggestion was to "group by hour" and SUM the counts and LISTAGG the roles coming from your existing query, EXACTLY AS WRITTEN, used as subquery. Why did you need to change the query you had in your original post?

chandra_1986

Thanks experts,

output is like:

02/10/2021:00 BS_MW_TECH,BS_SE_TECH,BS_SW_TECH,BS_W_TECH,DTV_HSP,DTV_TECH,IEFS_MW_TECH,IEFS_SE_TECH,IEFS_SW_TECH,IEFS_W_TECH,TFS_SE_TECH,TFS_SW_TECH,TFS_W_TECH 151

02/10/2021:01 BS_MW_TECH,BS_W_TECH,DTV_HSP,DTV_TECH,IEFS_MW_TECH,IEFS_SE_TECH,IEFS_SW_TECH,IEFS_W_TECH,TFS_SW_TECH,TFS_W_TECH 89

we have requirement like output from second row like values from 00 hours + 01 hour value:

so requested output like in second row(89+151)

02/10/2021:00 BS_MW_TECH,BS_SE_TECH,BS_SW_TECH,BS_W_TECH,DTV_HSP,DTV_TECH,IEFS_MW_TECH,IEFS_SE_TECH,IEFS_SW_TECH,IEFS_W_TECH,TFS_SE_TECH,TFS_SW_TECH,TFS_W_TECH 151

02/10/2021:01 BS_MW_TECH,BS_W_TECH,DTV_HSP,DTV_TECH,IEFS_MW_TECH,IEFS_SE_TECH,IEFS_SW_TECH,IEFS_W_TECH,TFS_SW_TECH,TFS_W_TECH 240

RogerT

So the requirement changes with every post? Just in case you need it ... i left the uid_count and added a running summary.

 select TO_CHAR(LOGIN_LOGOUT_TIME,'mm/dd/yyyy:hh24') AS LOGINLOGOUTTIME
       ,LISTAGG(AGENTROLE,',') within group (order by AGENTROLE) as RoleList
       ,MAX(UID_COUNT) AS UID_COUNT
       ,SUM(MAX(UID_COUNT)) OVER (ORDER BY TO_CHAR(LOGIN_LOGOUT_TIME,'mm/dd/yyyy:hh24')) AS UID_COUNT_SUM
 from (select DISTINCT trunc(login_logout_time,'HH24') as login_logout_time
             ,count(distinct "UID") over (PARTITION BY trunc(login_logout_time,'HH24')) as UID_COUNT
             ,AGENTROLE
         from at_agent
        where LOGIN_LOGOUT_TIME >= TRUNC(SYSDATE)
          and AGENTROLE 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 TO_CHAR(LOGIN_LOGOUT_TIME,'mm/dd/yyyy:hh24')
 order by LOGINLOGOUTTIME;

chandra_1986

getting below error while executing the query:-

ORA-00904: "LOGIN_LOGOUT_TIME": invalid identifier

00904. 00000 - "%s: invalid identifier"

*Cause:   

*Action:

Error at Line: 332 Column: 15