Getting average value for each day

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Getting average value for each day

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2011-02-24 : 15:45:38

I need to get average value for each day for each station/parameter/method/units.

I have these tables:
SAMPLE_DATA: CREATE TABLE [dbo].[SAMPLE_DATA](
[SAMPLE_NUM] [numeric](12, 0) IDENTITY(1,1) NOT NULL,(Primary Key)
[SAMPLE_TYPE] [char](1) NOT NULL,
[SAMPLE_STATION] [int] NOT NULL,
[SAMPLE_INTERVAL] [char](1) NOT NULL,
[SAMPLE_START_DATE] [datetime] NOT NULL,
[SAMPLE_START_TIME] [int] NOT NULL,
[SAMPLE_END_DATE] [datetime] NOT NULL,
[SAMPLE_END_TIME] [int] NOT NULL,
CONSTRAINT [PK_SAMPLE_DATA] PRIMARY KEY CLUSTERED
([SAMPLE_NUM] ASC))
And
CREATE TABLE [dbo].[STATION_DATA](
[DATA_ID] [int] IDENTITY(1,1) NOT NULL, (Primary Key)
[DATA_SAMPLE_NUM] [numeric](12, 0) NOT NULL, (Foreign Key)
[DATA_PARAMETER] [char](8) NOT NULL,
[DATA_METHOD] [char](3) NOT NULL,
[DATA_UNIT] [char](2) NOT NULL,
[DATA_DECIMAL] [numeric](1, 0) NULL,
[DATA_VALUE] [numeric](8, 4) NULL,
[DATA_FLAG] [char](1) NULL,
[DATA_AUDIT_CODE] [char](1) NULL,
)
In the Sample Data table - I have record for each hour of the day for station/type/intervals.
In the Station Data table - I have the parameter/method/unit and data values for each. these tables are linked by the Sample Number, so basically - in the 2nd table - I have different parameter/method/unit/values for each station/type/interval in the first table.

How can I get the average for each day (Sample Start and Sample End dates would be the same)?
For example - I tried the following:

SELECT SAMPLE_STATION,SAMPLE_START_DATE,DATA_PARAMETER,AVG(DATA_VALUE),DATA_FLAG,GUIDE_LEVEL
FROM SAMPLE_DATA 
INNER JOIN STATION_DATA ON SAMPLE_NUM = DATA_SAMPLE_NUM
INNER JOIN GUIDELINE_INFO ON GUIDE_PARAM = DATA_PARAMETER
WHERE SAMPLE_STATION = 1168 AND SAMPLE_START_DATE >= '1-Jan-2010' AND 
SAMPLE_END_DATE <= '31-Dec-2010' AND DATA_PARAMETER = '81104'
AND GUIDE_TIME = '7' AND SAMPLE_START_DATE = SAMPLE_END_DATE
GROUP BY SAMPLE_STATION,SAMPLE_START_DATE,DATA_PARAMETER,DATA_VALUE,DATA_FLAG,GUIDE_LEVEL,GUIDE_PRECISION
HAVING ROUND(AVG(DATA_VALUE),GUIDE_PRECISION) > GUIDE_LEVEL

And didn't work.
Definitely need some help.
THanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-24 : 18:08:04

Well, for starters, do you want the average value for each group of
SAMPLE_STATION,SAMPLE_START_DATE,DATA_PARAMETER,DATA_FLAG,GUIDE_LEVEL
since that's what you're going to get with your query as it is. But only for records
where the start day and end day are the same. You may just need to add the sample_start_date to your SELECT and GROUP BY clauses.

Jim

P.S. What do you mean by "didn't work"? Some sample data for each table and what you'd like the output to be would be very helpful.

Everyday I learn something that somebody else already knew

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-25 : 00:25:30

remove this check from the where clause of your query and check if you get any data

AND SAMPLE_START_DATE = SAMPLE_END_DATE

Cheers
MIK

sqlbug
Posting Yak Master

201 Posts

Posted - 2011-02-25 : 12:39:17

Hi Jim and Mik, the reason for the SAMPLE_START_DATE = SAMPLE_END_DATE is - I want average for each day. Because there are 24 hours of data for each day and SAMPLE_START_DATE / SAMPLE_END_DATE are same for all of them, I used that.
So - what I need to get is - Average for each START_DATE/STATION/PARAMETER/METHOD/UNIT.

Sample:
SAMPLE_STATION SAMPLE_START_DATE DATA_PARAMETER (No column name) DATA_FLAG GUIDE_LEVEL
1168 2010-01-20 00:00:00.000 81104 33.800000 NULL 30.0000
1168 2010-02-05 00:00:00.000 81104 30.100000 V 30.0000

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources