2 Replies Latest reply: Dec 20, 2013 12:45 AM by Hoek RSS

group by with date

user9338712 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points