Forum Stats

  • 3,783,752 Users
  • 2,254,826 Discussions
  • 7,880,537 Comments

Discussions

Query output as per request

2

Answers

  • chandra_1986
    chandra_1986 Member Posts: 285 Blue Ribbon

    Thanks experts, we need to include:


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

  • RogerT
    RogerT Member Posts: 1,855 Gold Trophy

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

    Alex Nuijten
  • chandra_1986
    chandra_1986 Member Posts: 285 Blue Ribbon

    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
    RogerT Member Posts: 1,855 Gold Trophy

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

    hth

  • mathguy
    mathguy Member Posts: 10,226 Blue Diamond
    edited Feb 10, 2021 4:59PM

    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
    chandra_1986 Member Posts: 285 Blue Ribbon

    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
    RogerT Member Posts: 1,855 Gold Trophy

    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
    chandra_1986 Member Posts: 285 Blue Ribbon

    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