AVG when using TOP 7 - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

AVG when using TOP 7

Author

Topic

Johnyalm
Starting Member

49 Posts

Posted - 2005-05-05 : 05:19:04

I would like to get average from a table, but inly for the last seven days. I´ve tried this but it doesn´t work.

SELECT TOP 7 AVG(a_tblTIMESERIES.floatvalue) AS AVG
FROM a_tblTIMESERIES INNER JOIN
a_tblTIMESERIES_TYPE ON a_tblTIMESERIES.id_timeseries_type = a_tblTIMESERIES_TYPE.id_timeseries_type
WHERE (a_tblTIMESERIES_TYPE.type = 'steps') AND (a_tblTIMESERIES.id_actors = 7217)
GROUP BY [date]
ORDER BY a_tblTIMESERIES.[date] DESC

I only get the floatvalue each day then, but I wold like to get ONE AVG value for the 7 days.

Is there any workaround anyone can help me with?

www.mirrorgate.com

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-05-05 : 05:28:11

Is this what your after

SELECT ~~TOP 7~~ AVG(a_tblTIMESERIES.floatvalue) AS AVG
FROM a_tblTIMESERIES INNER JOIN
a_tblTIMESERIES_TYPE ON a_tblTIMESERIES.id_timeseries_type = a_tblTIMESERIES_TYPE.id_timeseries_type
WHERE (a_tblTIMESERIES_TYPE.type = 'steps') AND (a_tblTIMESERIES.id_actors = 7217)
AND DATEDIFF(dd,a_tblTIMESERIES.[date],GETDATE()) <= 7
~~GROUP BY [date]~~
~~ORDER BY a_tblTIMESERIES.[date] DESC~~

Andy

Beauty is in the eyes of the beerholder

Johnyalm
Starting Member

49 Posts

Posted - 2005-05-05 : 05:39:54

Great! Very much appreciated. I wish you a great day, and thousands of kisses!

www.mirrorgate.com

Johnyalm
Starting Member

49 Posts

Posted - 2005-05-05 : 05:46:50

Hmm, it seems to be one problem with what you came up with!

The timeseries is not allways updated so when I checked the AVG for the actor there was only 4 records (the cust had not registered the data for some of the days). And I would like to get the average for the last 7 days/records. Not the AVG for 4 out of 7 possible (or less days).

Any solution for this problem?

www.mirrorgate.com

Johnyalm
Starting Member

49 Posts

Posted - 2005-05-05 : 05:51:27

Then I would like to get a solution on getting the AVG for the whole timeseries for the 14 lowest values, and the 14 highest values. I mean if there are 100 records, I would like to get a AVG for those 14 that has the lowest floatvalue and as well fot those that has the highest values?

Are there a smart solution an that as well?

www.mirrorgate.com

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-05-05 : 06:01:55

What about this then

SET NOCOUNT ON
DECLARE @LastDate datetime
SET @LastDate = (SELECT MAX([date]) FROM a_tblTIMESERIES)

SELECT AVG(a_tblTIMESERIES.floatvalue) AS AVG
FROM a_tblTIMESERIES INNER JOIN
a_tblTIMESERIES_TYPE ON a_tblTIMESERIES.id_timeseries_type = a_tblTIMESERIES_TYPE.id_timeseries_type
WHERE (a_tblTIMESERIES_TYPE.type = 'steps') AND (a_tblTIMESERIES.id_actors = 7217)
AND DATEDIFF(dd,a_tblTIMESERIES.[date],@LastDate) <= 7

Andy

Beauty is in the eyes of the beerholder

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-05-05 : 06:08:24

quote:
Originally posted by Johnyalm

Then I would like to get a solution on getting the AVG for the whole timeseries for the 14 lowest values, and the 14 highest values. I mean if there are 100 records, I would like to get a AVG for those 14 that has the lowest floatvalue and as well fot those that has the highest values?

Are there a smart solution an that as well?

www.mirrorgate.com

SET ROWCOUNT 14

SELECT AVG(a_tblTIMESERIES.floatvalue) AS AVG
FROM.................
ORDER BY a_tblTIMESERIES.floatvalue --DESC

SET ROWCOUNT 0

Andy

Beauty is in the eyes of the beerholder

Johnyalm
Starting Member

49 Posts

Posted - 2005-05-05 : 06:32:11

Thanks Andy!

There is still a problem when running this query:

SET ROWCOUNT 14
SELECT AVG(a_tblTIMESERIES.floatvalue) AS AVG
FROM a_tblTIMESERIES INNER JOIN
a_tblTIMESERIES_TYPE ON a_tblTIMESERIES.id_timeseries_type = a_tblTIMESERIES_TYPE.id_timeseries_type
WHERE (a_tblTIMESERIES_TYPE.type = 'steps') AND (a_tblTIMESERIES.id_actors = 7217)
ORDER BY a_tblTIMESERIES.floatvalue DESC
SET ROWCOUNT 0

I get error "a_tblTIMESERIES.floatvalue is invalid in the ORDER BY clause because it is not contaied in a aggregate function and there is no GROUP BY clause".

The other stuff worked great!

www.mirrorgate.com

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2005-05-05 : 08:34:56

I think this should do it for you:


SELECT AVG(AverageOf7Days) as WeeklyAverage
FROM
(
	SELECT TOP 7 AVG(a_tblTIMESERIES.floatvalue) AS AverageOf7Days
	FROM a_tblTIMESERIES INNER JOIN
	a_tblTIMESERIES_TYPE ON a_tblTIMESERIES.id_timeseries_type = a_tblTIMESERIES_TYPE.id_timeseries_type
	WHERE (a_tblTIMESERIES_TYPE.type = 'steps') AND (a_tblTIMESERIES.id_actors = 7217)
	GROUP BY [date]
	ORDER BY a_tblTIMESERIES.[date] DESC
)InnerQuery1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources