• Status: Solved
• Priority: Medium
• Security: Public
• Views: 206

# How can i calculate % avail in a query...

i have a query which returns 3 columns

capacity, used, avail

i would like to add a 4th one which calculates the % avail so the return should look like this

100   10  90    90%
100   18  92    92%

and so on, how can i do this...
0
AlexPonnath
• 8
• 5
1 Solution

Commented:
How about concatenating a % to the available?
0

Author Commented:
how would i do that ???
0

Commented:
Select capacity, used, avail, CAST(avail as varchar(20)) + '%'
From TableName
0

Author Commented:
now i get it put a % behind the avil...

that does not work since i just used a example which was simple as far as nbr goes

lets say if the data looks like this it wont work...

1000   10  990     99 %
500      5  495     99 %
0

Commented:
Select capacity, used, avail, avail * 100 / capacity
from TableName
0

Commented:
This may be more accurate:

Select capacity, used, avail, CAST(CAST(avail As decimal(18, 4)) * 100 / capacity As integer)
from @Temp
0

Commented:
This:
from @Temp

from TableName
0

Author Commented:
It works somewat the problem i have is with the acuracy of the calculation.

look at this data i have

1000  as capacity
987 as avail
the query returns 98%

but if i do this via calculator i get 98.7..

how can i fix that ?
0

Commented:
How many decimal places do you need?
0

Author Commented:
2 will do..

here is the code i use for the percent field in my query

cast(cast(sum(case when t2.status = 'Open' then 1 else 0 end)As decimal(18, 4)) * 100 / 1000 As integer)  as [Percent],
0

Commented:
Change this:
Select capacity, used, avail, CAST(CAST(avail As decimal(18, 4)) * 100 / capacity As integer)

To:
Select capacity, used, avail, CAST(CAST(avail As decimal(18, 2)) * 100 / capacity As decimal(18, 2))
0

Commented:
Change this:
cast(cast(sum(case when t2.status = 'Open' then 1 else 0 end)As decimal(18, 4)) * 100 / 1000 As integer)  as [Percent],

To:
cast(cast(sum(case when t2.status = 'Open' then 1 else 0 end)As decimal(18, 4)) * 100 / 1000 As decimal(18, 2))  as [Percent],
0

Author Commented:
that did the job...
0

## Featured Post

• 8
• 5
Tackle projects and never again get stuck behind a technical roadblock.