Query to calculate the percentage of occurance

[cdolphin88]

Hi,

I have a table where I need to calculate the number of occurance... I
used SELECT COUNT (band), but then I need to know the percentage which
I need something like band / COUNT (band)

Is it possible in Access to do this?

ex:

band 2 - number of occurance - 5 percentage would be 2/7= 28%
band 5 - number of occurance - 2 percentage would be 5/7 = 71%

 

[John Spencer]

Try something like this.

SELECT Band, Count(Band) as CountBand
, Count(Band) / (SELECT Count(Band) From YourTable) as Percentage
FROM YourTable
GROUP BY Band

If you have other limiting criteria, in the main query, you may need to
include that in the subquery also.

 

[cdolphin88]

Hi John,

It works, but if I want to display it with % at the end and I want if
the division = 0.66 it will show me 66%? Is that possible to times the
result by 100?

Cheers



John Spencer escreveu:

 

[John Spencer]

That is basically a formatting function. If you are doing this in the
query, you can format the result using the field property.

If you are using the query as the basis of a report, you can use the format
property of the relevant control.

You can use the format function in the query itself.
Format(Count(Band) / (SELECT Count(Band) From YourTable),"Percent") as
Percentage
Or
Format(Count(Band) / (SELECT Count(Band) From YourTable), "0%") as
Percentage

Remember that using the Format FUNCTION turns the results into a string.
Strings will sort differently than numbers.

String Order
1
10
11
2
23

Number order
1
2
10
11
23