[Solved] Can one calculate percentages?

Creating tables and queries

[Solved] Can one calculate percentages?

Postby clementchan1095 » Tue May 04, 2010 5:06 pm

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?

Postby eremmel » Tue May 04, 2010 9:11 pm

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 * ....'
Please, edit this thread's initial post and add "[Solved]" to the subject line if your problem has been solved.
OOo 3.3.0 on XP SP3 for real life with ORB; AOO 3.4.1, 4.0.1, LO 4.1.2.3 on W7 for testing
User avatar
eremmel
Volunteer
 
Posts: 656
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Can one calculate percentages?

Postby clementchan1095 » Wed May 05, 2010 3:59 pm

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?

Postby eremmel » Wed May 05, 2010 7:03 pm

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.
Please, edit this thread's initial post and add "[Solved]" to the subject line if your problem has been solved.
OOo 3.3.0 on XP SP3 for real life with ORB; AOO 3.4.1, 4.0.1, LO 4.1.2.3 on W7 for testing
User avatar
eremmel
Volunteer
 
Posts: 656
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Can one calculate percentages?

Postby clementchan1095 » Fri May 07, 2010 5:22 am

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?

Postby eremmel » Fri May 07, 2010 10:44 am

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"
Please, edit this thread's initial post and add "[Solved]" to the subject line if your problem has been solved.
OOo 3.3.0 on XP SP3 for real life with ORB; AOO 3.4.1, 4.0.1, LO 4.1.2.3 on W7 for testing
User avatar
eremmel
Volunteer
 
Posts: 656
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 2 guests