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 -

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

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');

So simple, yet so hard for a newbie. Thank you very much for your help. It is greatly appreciated.

-Zach