Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    54

    Unanswered: getting counts for each hour of a day

    Hi,

    I have a table called MQMD
    which has the following columns
    TSTAMP
    MSGID
    PUTDATE
    PUTTIME ...e.t.c

    I want out put as follows

    Time range Message Count
    --------------- ------------------
    00:00 - 00:59 1000
    01:00 - 01:59 2000
    02:00 - 02:59 349
    ....
    ....
    till the messages are available in the day

    Is it possible to achieve this result using SQL?

    I appreciate your help!!

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: getting counts for each hour of a day

    Originally posted by manth
    Hi,

    I have a table called MQMD
    which has the following columns
    TSTAMP
    MSGID
    PUTDATE
    PUTTIME ...e.t.c

    I want out put as follows

    Time range Message Count
    --------------- ------------------
    00:00 - 00:59 1000
    01:00 - 01:59 2000
    02:00 - 02:59 349
    ....
    ....
    till the messages are available in the day

    Is it possible to achieve this result using SQL?

    I appreciate your help!!

    Thanks
    Assuming your range is based on TSTAMP:

    select hour(tstamp), count(1) from mqmd where date(tstamp)=date(current timestamp) group by hour(tstamp);

  3. #3
    Join Date
    Mar 2004
    Posts
    54
    Excellent!!

    It's working nice, But i have a question, the query has return me the following result

    db2 => SELECT HOUR(TSTAMP), COUNT(MSGID) FROM DB2.MQMD WHERE DATE(TSTAMP)=DATE('2004-04-07-00.00.00') GROUP BY HOUR(TSTAMP)

    1 2
    ----------- -----------
    0 94
    1 96
    2 96
    3 96
    4 96
    5 96
    6 104
    7 145
    8 123

    9 record(s) selected.

    Does it mean that it has count from 00:00 - 08:59?

    Please clarify this!

    Thanks

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Yes ...

    Originally posted by manth
    Excellent!!

    It's working nice, But i have a question, the query has return me the following result

    db2 => SELECT HOUR(TSTAMP), COUNT(MSGID) FROM DB2.MQMD WHERE DATE(TSTAMP)=DATE('2004-04-07-00.00.00') GROUP BY HOUR(TSTAMP)

    1 2
    ----------- -----------
    0 94
    1 96
    2 96
    3 96
    4 96
    5 96
    6 104
    7 145
    8 123

    9 record(s) selected.

    Does it mean that it has count from 00:00 - 08:59?

    Please clarify this!

    Thanks
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Mar 2004
    Posts
    54

    Thumbs up

    Excellent job!!
    I appreciate it.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •