Results 1 to 5 of 5

Thread: Query Help

  1. #1
    Join Date
    Dec 2002
    Posts
    3

    Unanswered: Query Help

    I am trying to aggregate some values from a table, and need some assistance.

    I write a numeric value to the table once every hour of every day. The table contains data for an entire month. There is also a date field (DATETIME) that stores the record insert time. I would like to come up with a monthly average per hour. For example, I would like to average the sample from every day at 1AM.

    I'm having trouble just using the hour from the DATETIME field, and I also don't know if I should be trying this with the GROUP BY function, or with some type of nestd query. Any help you can provide is greatly appreciated.

    Thanks -

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Question Which database do you use ?

    Hello,

    can you please give us a hint, which databases you using ?
    In Oracle you can convert a date value with extracting the hout part by
    using TO_CHAR(date_field, 'HH') or TO_CHAR(date_field, HH24) for numeric conversion.

    Hope that helps ?

    Manfred Peter
    (Alligator COmpany GmbH)
    http://www.alligatorsql.com

  3. #3
    Join Date
    Dec 2002
    Posts
    3
    My apologies ... the database is Oracle.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by seilsz
    My apologies ... the database is Oracle.
    So your query will be something like:

    SELECT TO_CHAR(created_date,'HH24') hour, AVG( value )
    FROM table
    WHERE ...
    GROUP BY TO_CHAR(created_date,'HH24');

  5. #5
    Join Date
    Dec 2002
    Posts
    3
    So simple, yet so hard for a newbie. Thank you very much for your help. It is greatly appreciated.

    -Zach

Posting Permissions

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