Average Value Script

The table listed below is sampled every minute with a [CounterDateTime]
[char] (24) format.
I would like to create a script that will average the MarketValue,
FirstMarketValueA, and MarketCount fields in hourly format using the same
counterIDs.
Please help me create a script for average values.
Thanks,
CREATE TABLE [dbo].[Market_Jun2005] (
[MarketID] [int] NOT NULL ,
[RecordIndex] [int] NOT NULL ,
[CounterDateTime] [char] (24) NOT NULL ,
[MarketValue] [float] NOT NULL ,
[FirstMarketValueA] [int] NULL ,
[MarketCount] [int] NULL
)On Tue, 9 Aug 2005 14:35:44 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:

>The table listed below is sampled every minute with a [CounterDateTime]
>[char] (24) format.
>I would like to create a script that will average the MarketValue,
>FirstMarketValueA, and MarketCount fields in hourly format using the same
>counterIDs.
>Please help me create a script for average values.
Hi Joe,
First, change your CounterDateTime column to datatype datetime.
After that, you can use this query:
SELECT MarketID,
DATEADD(hour,
DATEDIFF(hour, '20040101', CounterDateTime),
'20040101') AS Hour,
AVG (MarketValue) AS AvgMarketValue,
AVG (FirstMarketValueA) AS AvgFirstMarketValueA,
AVG (MarketCount) AS AvgMarketCount
FROM Market)Jun2005
GROUP BY MarketIT,
DATEADD(hour,
DATEDIFF(hour, '20040101', CounterDateTime),
'20040101')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)