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

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?

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.

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))/(select count(*) from tablename)) as mycalcpercentage

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;

tx

Select 100 * Avg(Cast(Availability as Decimal)) from <Table>

The cast may not actually be needed, but try it both ways