[Solved] Can one calculate percentages?

Creating tables and queries
Post Reply
clementchan1095
Posts: 8
Joined: Mon Feb 22, 2010 3:18 pm

[Solved] Can one calculate percentages?

Post by clementchan1095 »

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
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Can one calculate percentages?

Post by eremmel »

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 * ....'
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
clementchan1095
Posts: 8
Joined: Mon Feb 22, 2010 3:18 pm

Re: Can one calculate percentages?

Post by clementchan1095 »

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
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Can one calculate percentages?

Post by eremmel »

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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
clementchan1095
Posts: 8
Joined: Mon Feb 22, 2010 3:18 pm

Re: Can one calculate percentages?

Post by clementchan1095 »

Thanks, but it is possible to set that as a default?
OpenOffice 3.0 for MAC
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Can one calculate percentages?

Post by eremmel »

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

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

select CAST( 10000 * ("DoubleField"/ (...) ) AS INT )  / 100.00  || '%' as "PercentFromDoubleField"
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Post Reply