Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 553
  • Last Modified:

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
0
karinos57
Asked:
karinos57
1 Solution
 
markmiddlemistCommented:
If I'm understanding correctly then it should just be

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

The cast may not actually be needed, but try it both ways
0
 
karinos57Author Commented:
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?
0
 
8080_DiverCommented:
Try:

SELECT   100.00 * SUM(Availability) / COUNT(Availability) = PctAvailable
FROM yourtable;
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
karinos57Author Commented:
but this is the same result as:
select Avg(Availability) from mytable
thanks but still not sure how this is done...
0
 
8080_DiverCommented:
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.
0
 
keyuCommented:
select (((select count(*) from tablename where Availability=1)*(100))/(select count(*) from tablename)) as mycalcpercentage
0
 
karinos57Author Commented:
thnaks guys, how could i round up to one decimal using this:
SELECT   100.00 * SUM(Availability) / COUNT(Availability) = PctAvailable
FROM yourtable;
0
 
8080_DiverCommented:
Try the following:

SELECT   INT(((1000.00 * SUM(Availability)) + .5) / COUNT(Availability)) / 10 = PctAvailable
FROM yourtable;
0
 
karinos57Author Commented:
tx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now