October 9, 2007 at 3:50 pm
I am pulling the average uptime of router for my company. I built a query to give me something like this:
name IP avg of availability
node 1 x.x.x.x 100
node 2 x.x.x.x 99
node 3 x.x.x.x 98
node 4 x.x.x.x 100
Now, I need to average the above numbers to get a single value, but it is not practical to use
(100+99+98+100)/4 because there are hundreds of them.
So, how do I get an average of the results of an average?
October 9, 2007 at 5:49 pm
First I'd drop this in the T-SQL discussion (moved).
Second, are you getting the average like this?
select ip, avg(time)
from mytable
group by ip
If so, you can wrap that.
select avg(a.ipavg)
from
( select b.ip, avg(b.time) as ipavg
from mytable b
group by ip
) a
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 10, 2007 at 6:48 am
Thank you SO much. It worked I think. Here is my Query.
select avg(a.availability) AS 'Average of Availabilty'
from
( select avg(b.availability) as Availability
from nodes n INNER JOIN Responsetime b on (n.nodeID = b.nodeID)
WHERE n.region LIKE 'region b'
group by n.caption
) a
Here is my Result: 99.3430829881605
Now, I can't add any extra columns though. Btw, the column I need to add is in a different table.
October 10, 2007 at 11:40 am
Using windowed aggregates (using the OVER clause) avoid the messy join-back stuff:
SELECT n.caption, AVG(b.availability) AS Availability, AVG(AVG(b.availability)) OVER () AS 'Average of Availability'
FROM nodes n INNER JOIN
Responsetime b ON n.nodeID = b.nodeID
WHERE n.region = 'region b'
GROUP BY n.caption
-Eddie
Eddie Wuerch
MCM: SQL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply