Hourly Average

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.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" <comcontrol@.aol.com>
wrote:

Quote:

Originally Posted by

>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:

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:

Quote:

Originally Posted by

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" <comcontrol@.aol.com>
wrote:
>

Quote:

Originally Posted by

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.

|||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" <comcontrol@.aol.com>
wrote:

Quote:

Originally Posted by

>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:

Quote:

Originally Posted by

>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" <comcontrol@.aol.com>
>wrote:
>>

Quote:

Originally Posted by

>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.

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

Quote:

Originally Posted by

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

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

Quote:

Originally Posted by

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|||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:

Quote:

Originally Posted by

>[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.

Quote:

Originally Posted by

>

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:

Quote:

Originally Posted by

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

Quote:

Originally Posted by

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

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

Quote:

Originally Posted by

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

|||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:

Quote:

Originally Posted by

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:
>

Quote:

Originally Posted by

[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:

Quote:

Originally Posted by

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

Quote:

Originally Posted by

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

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

Quote:

Originally Posted by

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

|||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:

Quote:

Originally Posted by

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:
>

Quote:

Originally Posted by

[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:

Quote:

Originally Posted by

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

Quote:

Originally Posted by

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

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

Quote:

Originally Posted by

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