2 Replies Latest reply: Dec 20, 2013 3:08 AM by SKP RSS

    group by with date

    user9338712

      Dear all,

       

       

      I have a table which has the below informaiton

      logon_time (in timestamp format)
      program  (varchar)
      machine (varchar)

      Now, I want to retrieve one day record from the above table

      select logon_time,program,machine from  tabsbkp.monitor_sessions where trunc(logon_time)=trunc(sysdate-1) group by machine,logon_time,program;

      How do I change the above to get the number of records per hour

      Date   Program  Machine
      19 Dec 2013 1AM -  sqlplus  server1
      19 Dec 2013 2AM -  sqlplus  server2


      Please advise.

        • 1. Re: group by with date
          Hoek

          Try something like:

           

          select to_char(logon_time, 'dd-mm-yyyy hh24') logon_time

          ,      program

          ,      machine

          from   tabsbkp.monitor_sessions

          where  trunc(logon_time)=trunc(sysdate-1)

          group by to_char(logon_time, 'dd-mm-yyyy hh24')

                 , program

                 , machine;

          If that doesn't work then post an example (DDL+DML) as explained here:

          Re: 2. How do I ask a question on the forums?

          • 2. Re: group by with date
            SKP

            Try this query:

            -----------------------

             

            with t as

            (

            select to_timestamp_tz('19-DEC-13 11.27.38.278000000 AM +05:30','DD-MON-RR HH:MI:SS.FF  AM TZH:TZM')logon_time,'SQLPLUS' program,'server1' machine from dual

            union all

            select to_timestamp_tz('20-DEC-13 07.57.38.278000000 AM +05:30','DD-MON-RR HH:MI:SS.FF  AM TZH:TZM')logon_time,'SQLPLUS' program,'server3' machine from dual

            union all

            select to_timestamp_tz('20-DEC-13 06.47.38.278000000 AM +05:30','DD-MON-RR HH:MI:SS.FF  AM TZH:TZM')logon_time,'SQLPLUS' program,'server1' machine from dual

            union all

            select to_timestamp_tz('19-DEC-13 11.37.38.278000000 AM +05:30','DD-MON-RR HH:MI:SS.FF  AM TZH:TZM')logon_time,'SQLPLUS' program,'server1' machine from dual

            union all

            select to_timestamp_tz('20-DEC-13 08.23.38.278000000 AM +05:30','DD-MON-RR HH:MI:SS.FF  AM TZH:TZM')logon_time,'SQLPLUS' program,'server2' machine from dual

            union all

            select to_timestamp_tz('20-DEC-13 08.27.38.278000000 AM +05:30','DD-MON-RR HH:MI:SS.FF  AM TZH:TZM')logon_time,'SQLPLUS' program,'server1' machine from dual

            union all

            select to_timestamp_tz('20-DEC-13 08.27.38.278000000 AM +05:30','DD-MON-RR HH:MI:SS.FF  AM TZH:TZM')logon_time,'SQLPLUS' program,'server1' machine from dual

            union all

            select to_timestamp_tz('20-DEC-13 09.27.38.278000000 AM +05:30','DD-MON-RR HH:MI:SS.FF  AM TZH:TZM')logon_time,'SQLPLUS' program,'server1' machine from dual

            )

             

            select to_char(logon_time, 'dd-mm-yyyy hh24') logon_time

                  ,program

                  ,machine

                  ,count(1) NUM_CONN

            from t

            group by to_char(logon_time, 'dd-mm-yyyy hh24') , program, machine;

             

             

            LOGON_TIME   PROGRAM   MACHINE NUM_NODE

            --------------------   -----------------   --------------   --------------------

            19-12-2013 11    SQLPLUS    server1       2

            20-12-2013 08    SQLPLUS    server1       2

            20-12-2013 09    SQLPLUS    server1       1

            20-12-2013 06    SQLPLUS    server1       1

            20-12-2013 07    SQLPLUS    server3       1

            20-12-2013 08    SQLPLUS    server2       1