Average of an average - 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

General SQL Server Forums

New to SQL Server Programming

Average of an average

Author

Topic

accessdbguru
Starting Member

26 Posts

Posted - 2012-02-09 : 13:04:38

I need to get an average of all the average percentages. Result should be only one column with the average percent round to 2 decimal places.

here is the sql:

USE ZAMANA
SELECT
fairy.ABCD_ApplicationStatus_Daily.ApplicationID,
AVG(fairy.ABCD_ApplicationStatus_Daily.PercentAvailability) AS AVERAGE,
fairy.ABCD_Application.Name AS Application_Name
FROM
fairy.ABCD_ApplicationStatus_Daily
INNER JOIN
fairy.ABCD_Application ON fairy.ABCD_ApplicationStatus_Daily.ApplicationID = fairy.ABCD_Application.ID
WHERE
(fairy.ABCD_ApplicationStatus_Daily.Timestamp >= DATEADD(day, DATEDIFF(day, 30, GETDATE()) - 30, 0))
AND
(fairy.ABCD_ApplicationStatus_Daily.Timestamp < DATEADD(day, DATEDIFF(day, 30, GETDATE()) + 1, 0))
GROUP BY ApplicationID, fairy.ABCD_Application.Name
ORDER BY ApplicationID ASC

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-09 : 13:07:34

select avg(AVERAGE) from
(SELECT
fairy.ABCD_ApplicationStatus_Daily.ApplicationID,
AVG(fairy.ABCD_ApplicationStatus_Daily.PercentAvailability) AS AVERAGE,
fairy.ABCD_Application.Name AS Application_Name
FROM
fairy.ABCD_ApplicationStatus_Daily
INNER JOIN
fairy.ABCD_Application ON fairy.ABCD_ApplicationStatus_Daily.ApplicationID = fairy.ABCD_Application.ID
WHERE
(fairy.ABCD_ApplicationStatus_Daily.Timestamp >= DATEADD(day, DATEDIFF(day, 30, GETDATE()) - 30, 0))
AND
(fairy.ABCD_ApplicationStatus_Daily.Timestamp < DATEADD(day, DATEDIFF(day, 30, GETDATE()) + 1, 0))
GROUP BY ApplicationID, fairy.ABCD_Application.Name
)dt

No, you're never too old to Yak'n'Roll if you're too young to die.

accessdbguru
Starting Member

26 Posts

Posted - 2012-02-09 : 13:27:29

Looks great but the percentage value is not rounded to 2 decimal places and add a "%" sign to it as well. can you please help?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-09 : 13:30:35

select convert(varchar(10),round(avg(AVERAGE),2))+'%' from ...

No, you're never too old to Yak'n'Roll if you're too young to die.

accessdbguru
Starting Member

26 Posts

Posted - 2012-02-09 : 13:37:32

not working:

select convert(varchar(10),round(avg(AVERAGE),2))+'%' from
(
select avg(AVERAGE) from
(SELECT
fairy.ABCD_ApplicationStatus_Daily.ApplicationID,
AVG(fairy.ABCD_ApplicationStatus_Daily.PercentAvailability) AS AVERAGE,
fairy.ABCD_Application.Name AS Application_Name
FROM
fairy.ABCD_ApplicationStatus_Daily
INNER JOIN
fairy.ABCD_Application ON fairy.ABCD_ApplicationStatus_Daily.ApplicationID = fairy.ABCD_Application.ID
WHERE
(fairy.ABCD_ApplicationStatus_Daily.Timestamp >= DATEADD(day, DATEDIFF(day, 30, GETDATE()) - 30, 0))
AND
(fairy.ABCD_ApplicationStatus_Daily.Timestamp < DATEADD(day, DATEDIFF(day, 30, GETDATE()) + 1, 0))
GROUP BY ApplicationID, fairy.ABCD_Application.Name
)dt

)

accessdbguru
Starting Member

26 Posts

Posted - 2012-02-09 : 13:46:35

Sorry I got it now... thanks,,,,,

accessdbguru
Starting Member

26 Posts

Posted - 2012-02-09 : 13:47:20

select select convert(varchar(10),round(avg(AVERAGE),2))+'%' from
(
from
(SELECT
fairy.ABCD_ApplicationStatus_Daily.ApplicationID,
AVG(fairy.ABCD_ApplicationStatus_Daily.PercentAvailability) AS AVERAGE,
fairy.ABCD_Application.Name AS Application_Name
FROM
fairy.ABCD_ApplicationStatus_Daily
INNER JOIN
fairy.ABCD_Application ON fairy.ABCD_ApplicationStatus_Daily.ApplicationID = fairy.ABCD_Application.ID
WHERE
(fairy.ABCD_ApplicationStatus_Daily.Timestamp >= DATEADD(day, DATEDIFF(day, 30, GETDATE()) - 30, 0))
AND
(fairy.ABCD_ApplicationStatus_Daily.Timestamp < DATEADD(day, DATEDIFF(day, 30, GETDATE()) + 1, 0))
GROUP BY ApplicationID, fairy.ABCD_Application.Name
)dt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-09 : 14:17:52

quote:
Originally posted by accessdbguru

select ~~select~~ convert(varchar(10),round(avg(AVERAGE),2))+'%' from
(
from
(SELECT
fairy.ABCD_ApplicationStatus_Daily.ApplicationID,
AVG(fairy.ABCD_ApplicationStatus_Daily.PercentAvailability) AS AVERAGE,
fairy.ABCD_Application.Name AS Application_Name
FROM
fairy.ABCD_ApplicationStatus_Daily
INNER JOIN
fairy.ABCD_Application ON fairy.ABCD_ApplicationStatus_Daily.ApplicationID = fairy.ABCD_Application.ID
WHERE
(fairy.ABCD_ApplicationStatus_Daily.Timestamp >= DATEADD(day, DATEDIFF(day, 30, GETDATE()) - 30, 0))
AND
(fairy.ABCD_ApplicationStatus_Daily.Timestamp < DATEADD(day, DATEDIFF(day, 30, GETDATE()) + 1, 0))
GROUP BY ApplicationID, fairy.ABCD_Application.Name
)dt

didnt understand why you're doing formatting part also in sql query? you can very well achieve format (% sign with two decimal places) in your front end application code. SQL is not the right place to do it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources