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
karinos57Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.