Hourly Average

[Compliance]

Apologies for the simplicity of the question, but it reflects my
capabilities! I have the following sample fields coming from different
tables:

Location
TimeDate (timestamp)
Data

I need to return the average of Data per Location per HOUR.

Thanks.

[Roy Harvey]

I believe this will do it.

SELECT Location,
dateadd(hour,datediff(hour,0,TimeDate),0) as Hourly,
avg(Data) as AvgData
FROM .....
GROUP BY
Location,
dateadd(hour,datediff(hour,0,TimeDate),0)

Roy Harvey
Beacon Falls, CT

On 17 Aug 2006 09:31:32 -0700, "Compliance" <co********@aol.com>
wrote:

>Apologies for the simplicity of the question, but it reflects my
capabilities! I have the following sample fields coming from different
tables:

Location
TimeDate (timestamp)
Data

I need to return the average of Data per Location per HOUR.

Thanks.

[Compliance]

Roy:

Thanks for the reply! I have run this and am getting a syntax error on
the second line which says there is incorrect syntax near the '(' . I
played with spacing but could not resolve. Do you have any tips?
Also, is it inappropriate

Roy Harvey wrote:

I believe this will do it.

SELECT Location,
dateadd(hour,datediff(hour,0,TimeDate),0) as Hourly,
avg(Data) as AvgData
FROM .....
GROUP BY
Location,
dateadd(hour,datediff(hour,0,TimeDate),0)

Roy Harvey
Beacon Falls, CT

On 17 Aug 2006 09:31:32 -0700, "Compliance" <co********@aol.com>
wrote:

Apologies for the simplicity of the question, but it reflects my
capabilities! I have the following sample fields coming from different
tables:

Location
TimeDate (timestamp)
Data

I need to return the average of Data per Location per HOUR.

Thanks.

[Roy Harvey]

I checked the syntax and what I posted seems clean, other than the
lack of the FROM clause. I adapted it to a meaningess example using
an actual table:

SELECT id,
dateadd(hour,datediff(hour,0,crdate),0) as Hourly,
avg(schema_ver) as AvgData
FROM sysobjects
GROUP BY
id,
dateadd(hour,datediff(hour,0,crdate),0)

If you can't find the problem, past the exact code that is not
executing into your reply.

Roy Harvey
Beacon Falls, CT

On 17 Aug 2006 11:24:46 -0700, "Compliance" <co********@aol.com>
wrote:

>Roy:

Thanks for the reply! I have run this and am getting a syntax error on
the second line which says there is incorrect syntax near the '(' . I
played with spacing but could not resolve. Do you have any tips?
Also, is it inappropriate

Roy Harvey wrote:

>I believe this will do it.

SELECT Location,
dateadd(hour,datediff(hour,0,TimeDate),0) as Hourly,
avg(Data) as AvgData
FROM .....
GROUP BY
Location,
dateadd(hour,datediff(hour,0,TimeDate),0)

Roy Harvey
Beacon Falls, CT

On 17 Aug 2006 09:31:32 -0700, "Compliance" <co********@aol.com>
wrote:

>Apologies for the simplicity of the question, but it reflects my
capabilities! I have the following sample fields coming from different
tables:

Location
TimeDate (timestamp)
Data

I need to return the average of Data per Location per HOUR.

Thanks.

[Roy Harvey]

On Thu, 17 Aug 2006 12:02:49 -0700, you wrote:

SELECT tblRecord.HistAt
dateadd(hour,datediff(hour,0,HistAt),0) as Hourly,

On Thu, 17 Aug 2006 12:02:49 -0700, "Compliance" <co********@aol.com>
wrote:

SELECT tblRecord.HistAt
dateadd(hour,datediff(hour,0,HistAt),0) as Hourly,

There is a comma missing at the end of the first line.

Roy Harvey
Beacon Falls, CT

[Compliance]

My mistake...thanks. Now I am getting the following back, but its not
quite correct. See, unless I place the DateTime field in the GROUPBY I
get an error:

>[Error] Script lines: 1-9 --------------------------

Column 'Date/Time' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause. 

>

So when my data comes back i get the actual TimeDate and the new
Hourly, but not the summarized hourly average (AvgData) alone:

DateTime Hourly AvgData
Name
--------------------- ---------------------
------------------ ------------
7/25/2006 9:30:06 PM 7/25/2006 9:00:00 PM 82.80000 1Name
7/25/2006 9:32:03 PM 7/25/2006 9:00:00 PM 82.40000 1Name
7/25/2006 9:34:09 PM 7/25/2006 9:00:00 PM 82.09999 1TName
Thanks again.

Roy Harvey wrote:

On Thu, 17 Aug 2006 12:02:49 -0700, you wrote:

SELECT tblRecord.HistAt
dateadd(hour,datediff(hour,0,HistAt),0) as Hourly,

On Thu, 17 Aug 2006 12:02:49 -0700, "Compliance" <co********@aol.com>
wrote:

SELECT tblRecord.HistAt
dateadd(hour,datediff(hour,0,HistAt),0) as Hourly,

There is a comma missing at the end of the first line.

Roy Harvey
Beacon Falls, CT

[Compliance]

Got it working now...I was retuening the DateTime data in the Select so
it was just giving me what I asked for.

Thanks Very Much Roy.
Compliance wrote:

My mistake...thanks. Now I am getting the following back, but its not
quite correct. See, unless I place the DateTime field in the GROUPBY I
get an error:

[Error] Script lines: 1-9 --------------------------

Column 'Date/Time' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause. 

So when my data comes back i get the actual TimeDate and the new
Hourly, but not the summarized hourly average (AvgData) alone:

DateTime Hourly AvgData
Name
--------------------- ---------------------
------------------ ------------
7/25/2006 9:30:06 PM 7/25/2006 9:00:00 PM 82.80000 1Name
7/25/2006 9:32:03 PM 7/25/2006 9:00:00 PM 82.40000 1Name
7/25/2006 9:34:09 PM 7/25/2006 9:00:00 PM 82.09999 1TName
Thanks again.

Roy Harvey wrote:

On Thu, 17 Aug 2006 12:02:49 -0700, you wrote:

SELECT tblRecord.HistAt
dateadd(hour,datediff(hour,0,HistAt),0) as Hourly,

On Thu, 17 Aug 2006 12:02:49 -0700, "Compliance" <co********@aol.com>
wrote:

SELECT tblRecord.HistAt
dateadd(hour,datediff(hour,0,HistAt),0) as Hourly,

There is a comma missing at the end of the first line.

Roy Harvey
Beacon Falls, CT

[Compliance]

Got it working now...I was retuening the DateTime data in the Select so
it was just giving me what I asked for.

Thanks Very Much Roy.
Compliance wrote:

My mistake...thanks. Now I am getting the following back, but its not
quite correct. See, unless I place the DateTime field in the GROUPBY I
get an error:

[Error] Script lines: 1-9 --------------------------

Column 'Date/Time' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause. 

So when my data comes back i get the actual TimeDate and the new
Hourly, but not the summarized hourly average (AvgData) alone:

DateTime Hourly AvgData
Name
--------------------- ---------------------
------------------ ------------
7/25/2006 9:30:06 PM 7/25/2006 9:00:00 PM 82.80000 1Name
7/25/2006 9:32:03 PM 7/25/2006 9:00:00 PM 82.40000 1Name
7/25/2006 9:34:09 PM 7/25/2006 9:00:00 PM 82.09999 1TName
Thanks again.

Roy Harvey wrote:

On Thu, 17 Aug 2006 12:02:49 -0700, you wrote:

SELECT tblRecord.HistAt
dateadd(hour,datediff(hour,0,HistAt),0) as Hourly,

On Thu, 17 Aug 2006 12:02:49 -0700, "Compliance" <co********@aol.com>
wrote:

SELECT tblRecord.HistAt
dateadd(hour,datediff(hour,0,HistAt),0) as Hourly,

There is a comma missing at the end of the first line.

Roy Harvey
Beacon Falls, CT