## [Solved] Can one calculate percentages?

Creating tables and queries

### [Solved] Can one calculate percentages?

I know how to do simple calculations like addition and division in the query, but the division seems only to produce answers to the nearest whole number. I would like to calculate percentage (i.e. dividing a number by another and multiplying by 100%) , and how would I be able to do that?

Thanks
Last edited by clementchan1095 on Tue May 11, 2010 3:53 am, edited 1 time in total.
OpenOffice 3.0 for MAC
clementchan1095

Posts: 8
Joined: Mon Feb 22, 2010 3:18 pm

### Re: Can one calculate percentages?

Expressions are evaluated from left to right (but with precedence of operator). The first (left) value determines the type for calculation. So when your data type is Integer like and you start with dividing the number you loose precision and the multiply by 100 does not help. So start your expression with with ' 100 * "Field" / "Total" '. When you need more accuracy then e.g. 24%, like 25.4%, start your calculation with ' 100.0 * ....'
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians

eremmel
Volunteer

Posts: 895
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

### Re: Can one calculate percentages?

Thankyou, I understand the precision issue, but my current main problem lies in the basic step of displaying it as an percentage. When I tried to calculate it in a query, how do I set the output to be in percentage format and how do I set the numbers of decimals shown?
OpenOffice 3.0 for MAC
clementchan1095

Posts: 8
Joined: Mon Feb 22, 2010 3:18 pm

### Re: Can one calculate percentages?

The way things are displayed is in general not something you handle in a query; it is something for the presentation layer. So when you run your query you can select the column heading and select the first option when you press with your right-mouse button. There you can configure your formatting. The same applies to forms and reports.
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians

eremmel
Volunteer

Posts: 895
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

### Re: Can one calculate percentages?

Thanks, but it is possible to set that as a default?
OpenOffice 3.0 for MAC
clementchan1095

Posts: 8
Joined: Mon Feb 22, 2010 3:18 pm

### Re: Can one calculate percentages?

The column of your result set containing the calculated percentage is not a table field. So a default formatting (if possible) would involve chancing formatting of a global default, but that is not what you want. You might convert the int / double value to a string by appending '%' to the result, but you have to do some tricks to reduce the number of decimals for double values. E.g. for 2 decimals with integer:
Code: Select all   Expand viewCollapse view
`select 100.00 * "IntField" / ( ... ) || '%' as "PercentFromIntField", ...`

For double values you have to force rounding via a cast to INT type again with 2 decimals. This only works with 'Direct SQL mode':
Code: Select all   Expand viewCollapse view
`select CAST( 10000 * ("DoubleField"/ (...) ) AS INT )  / 100.00  || '%' as "PercentFromDoubleField"`
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians

eremmel
Volunteer

Posts: 895
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands