asked on calculate availability using sql
Hi,
i just need to calculate the percentage that application is available during a given date. I am basically calculating the scores and availability so i am already calculating the scores but would like to add the availability using this methodology:
i have column called Availability:
Availability
1
1
1
0
0
0
1
1
Note, 1 means application is available and 0 means it is not available. So if i take the Avg of all these 8 entries, then my score will be about 62.5%. So how would i calculate if i just need to know what was the percentage that the application was up during that day using sql? thanks
i just need to calculate the percentage that application is available during a given date. I am basically calculating the scores and availability so i am already calculating the scores but would like to add the availability using this methodology:
i have column called Availability:
Availability
1
1
1
0
0
0
1
1
Note, 1 means application is available and 0 means it is not available. So if i take the Avg of all these 8 entries, then my score will be about 62.5%. So how would i calculate if i just need to know what was the percentage that the application was up during that day using sql? thanks
Microsoft SQL Server 2005Microsoft SQL Server 2008SQL
Last Comment
karinos57
ASKER
but this is what i am already doing in order to get the Scores. Keep in mind 1 means it is available and 0 means it is not available. In my case, i only need the available times as percentage. In other words, there was 8 times that we checked if the application is up and we found that 3 of the eight times was down. so i only need what is percentage was the up time?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
but this is the same result as:
select Avg(Availability) from mytable
thanks but still not sure how this is done...
select Avg(Availability) from mytable
thanks but still not sure how this is done...
The way you have set up the probem, the % of the time that the app was up during the day is the same thing as the Average availability but multiplied times 100 (to turn it from a decimal fraction to a percentage). If the average availability is 0.75, meaning that 18 of the 24 hours, the app was up (and, therefore, there were 18 "1's" and 6 "0's") then the percentageof time available would be 75%. That is the definition of % available as well as the definition of the average.
select (((select count(*) from tablename where Availability=1)*(100))/(se
ASKER
thnaks guys, how could i round up to one decimal using this:
SELECT 100.00 * SUM(Availability) / COUNT(Availability) = PctAvailable
FROM yourtable;
SELECT 100.00 * SUM(Availability) / COUNT(Availability) = PctAvailable
FROM yourtable;
Try the following:
SELECT INT(((1000.00 * SUM(Availability)) + .5) / COUNT(Availability)) / 10 = PctAvailable
FROM yourtable;
SELECT INT(((1000.00 * SUM(Availability)) + .5) / COUNT(Availability)) / 10 = PctAvailable
FROM yourtable;
ASKER
tx
Select 100 * Avg(Cast(Availability as Decimal)) from <Table>
The cast may not actually be needed, but try it both ways