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. 7 9806
I believe this will do it.
SELECT Location,
dateadd(hour,da tediff(hour,0,T imeDate),0) as Hourly,
avg(Data) as AvgData
FROM .....
GROUP BY
Location,
dateadd(hour,da tediff(hour,0,T imeDate),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:
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,da tediff(hour,0,T imeDate),0) as Hourly,
avg(Data) as AvgData
FROM .....
GROUP BY
Location,
dateadd(hour,da tediff(hour,0,T imeDate),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.
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,da tediff(hour,0,c rdate),0) as Hourly,
avg(schema_ver) as AvgData
FROM sysobjects
GROUP BY
id,
dateadd(hour,da tediff(hour,0,c rdate),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,da tediff(hour,0,T imeDate),0) as Hourly, avg(Data) as AvgData FROM ..... GROUP BY Location, dateadd(hour,da tediff(hour,0,T imeDate),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.
On Thu, 17 Aug 2006 12:02:49 -0700, you wrote:
SELECT tblRecord.HistA t
dateadd(hour,da tediff(hour,0,H istAt),0) as Hourly,
On Thu, 17 Aug 2006 12:02:49 -0700, "Compliance " <co********@aol .com>
wrote:
SELECT tblRecord.HistA t
dateadd(hour,da tediff(hour,0,H istAt),0) as Hourly,
There is a comma missing at the end of the first line.
Roy Harvey
Beacon Falls, CT
My mistake...thank s. 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.HistA t
dateadd(hour,da tediff(hour,0,H istAt),0) as Hourly,
On Thu, 17 Aug 2006 12:02:49 -0700, "Compliance " <co********@aol .com>
wrote:
SELECT tblRecord.HistA t
dateadd(hour,da tediff(hour,0,H istAt),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:
My mistake...thank s. 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.HistA t
dateadd(hour,da tediff(hour,0,H istAt),0) as Hourly,
On Thu, 17 Aug 2006 12:02:49 -0700, "Compliance " <co********@aol .com>
wrote:
SELECT tblRecord.HistA t
dateadd(hour,da tediff(hour,0,H istAt),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:
My mistake...thank s. 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.HistA t
dateadd(hour,da tediff(hour,0,H istAt),0) as Hourly,
On Thu, 17 Aug 2006 12:02:49 -0700, "Compliance " <co********@aol .com>
wrote:
SELECT tblRecord.HistA t
dateadd(hour,da tediff(hour,0,H istAt),0) as Hourly,
There is a comma missing at the end of the first line.
Roy Harvey
Beacon Falls, CT
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Philip D Heady |
last post by:
I'm in need of a good PHP programmer to help me with my lodgings website
asap. Someone who can work on it during the day for a few hrs while I'm at
work would be great. Prefer they live in USA eastern standard time zone.
Please send me your hourly rate, slill level, website references, and
availability asap.
Thank you.
Philip D. Heady
|
by: Anthony |
last post by:
First time here so please bear with me.
Set up a DTS package to export data to an excel sheet on an hourly
basis. Problem is, it keeps appending to the same excel sheet.
Any idea how to prevent that. All I want to accomplish is that every
hour, the latest data is in the excel sheet and the previous data is
deleted.
Thanks in advance!
|
by: Sam |
last post by:
Hello everyone,
I have around 20 reports in an ASP web-application which connects to a
SQL Server 2000 dB, executes stored procedures based on input
parameters and returns the data in a nice tabular format.
The data which is used in these reports actually originates from a 3rd
party accounting application called Exchequer. I have written a VB
application (I call it the extractor) which extracts data from
Exchequer and dumps the same...
|
by: Brett |
last post by:
For those of you working hourly, are you paid time and a half for anything
over 40 hours? Say you are making $50/hour. Once you go over 40, your rate
goes up to $75.
If so, do you find it difficult to get that rate? In other words, does
management hangle and bicker with you and in the end make you end up working
for the $50 rate?
How practical is it for a skilled C# developer to work from contract to
contract? Short term: 3 - 9...
|
by: C++Geek |
last post by:
I need to get this program to average the salaries. What am I doing
wrong?
//Program to read in employee data and calculate the average salaries
of the emplyees.
| |
by: danceli |
last post by:
I have two servers, using SQL server 2000.
I was asked for implementing hourly Backup 3 databases in one server
and restore those databases to another server.
Could anyone give me the detailed steps to do that?
Thanks a lot in advance!
|
by: MPD |
last post by:
Hi
How can I create a job in sql agent to create a new snapshot every hour?
I have, for eg a T-SQL that does it manually.
create database Snapshotter_snap_20070418_1821 on
( name = Snapshotter, filename =
'c:\temp\Snapshotter_snap_20070418_1821.ss')
as snapshot of Snapshotter
|
by: tommo5 |
last post by:
Please help! im new to SQL and im finding a way of calculating an hourly avg difficult.
This is what i have so far for all the data for the week - but i want an hourly avg instead of every second of everyday between. Please help! thanks
select "computerName" , "TimeSampled", "PerformanceCounterName", "SampledValue"
from dbo.SDKPerformanceview
where computername like 'dmm%' and performancecountername like 'total messages submitted'...
|
by: tommo5 |
last post by:
Please help! im new to SQL and im finding a way of calculating an hourly avg difficult.
This is what i have so far for all the data for the week - but i want an hourly avg instead of every second of everyday between. Please help! thanks
select "computerName" , "TimeSampled", "PerformanceCounterName", "SampledValue"
from dbo.SDKPerformanceview
where computername like 'dmm%' and performancecountername like 'total messages submitted'
and...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |