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
[Solved] Can one calculate percentages?
-
- Posts: 8
- Joined: Mon Feb 22, 2010 3:18 pm
[Solved] Can one calculate percentages?
Last edited by clementchan1095 on Tue May 11, 2010 3:53 am, edited 1 time in total.
OpenOffice 3.0 for MAC
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 * ....'
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
-
- Posts: 8
- Joined: Mon Feb 22, 2010 3:18 pm
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
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.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
W11 22H2 (build 22621), LO 7.4.2.3(x64)
-
- Posts: 8
- Joined: Mon Feb 22, 2010 3:18 pm
Re: Can one calculate percentages?
Thanks, but it is possible to set that as a default?
OpenOffice 3.0 for MAC
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:
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 100.00 * "IntField" / ( ... ) || '%' as "PercentFromIntField", ...
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)
W11 22H2 (build 22621), LO 7.4.2.3(x64)