## [Solved] Query SUM rounding to nearest whole number

Creating tables and queries

### [Solved] Query SUM rounding to nearest whole number

I am using a Query to establish the % of use for an item.

It seems to round fractions to 1 or 0 and when I re-format the column in the displayed query I get 1.00 and 0.00 instead of 0.25 & 0.75.

It works as a "workaround" when I multiply the result by 100 (getting 25 & 75), but there must be a way to keep X.XXX decimal places.
Last edited by Matt123 on Wed Apr 06, 2011 1:42 am, edited 1 time in total.
OpenOffice 3.3.0 on 32-bit Windows 7
OOBase, HSQLDB
Matt123

Posts: 35
Joined: Fri Apr 01, 2011 4:20 am

### Re: Query SUM rounding to nearest whole number

Why don't you copy the SQL statement of your query here?
It's nearly impossible to guess what you are doing? Running this query one one table that represents your stock of items?
Are you using something like "SELECT (...)/count(*) As percentage FROM stock" ?
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer

Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

### Re: Query SUM rounding to nearest whole number

ok

Code: Select all   Expand viewCollapse view
`SELECT "Q_FleetComposition"."FleetName",   "Q_FleetComposition"."Streamlining",    SUM( "Q_FleetComposition"."Fuel Capacity" * 100 / "Q_FleetTotals"."FleetFuelCap" ) AS "Percent"FROM "Q_FleetTotals",   "Q_FleetComposition"WHERE "Q_FleetTotals"."FleetName" = "Q_FleetComposition"."FleetName"GROUP BY "Q_FleetComposition"."FleetName",    "Q_FleetComposition"."Streamlining" ORDER BY "Q_FleetComposition"."FleetName" ASC,    "Q_FleetComposition"."Streamlining" ASC`

The table result looks like (dots merely to aid formatting);

FleetName......Streamlining......Percent
A........................S................75
A........................U................25
B........................S................100
C........................S................25
C........................U................75

The workaround works (using the " *100 " in the equation), but it is clumsy & I have other operations to do that also need decimal places.

& it just seems "odd" that I can't get a decimal result & it automatically rounds up or down to 1 or 0.

Cheers
Matt
OpenOffice 3.3.0 on 32-bit Windows 7
OOBase, HSQLDB
Matt123

Posts: 35
Joined: Fri Apr 01, 2011 4:20 am

### Re: Query SUM rounding to nearest whole number

Try ... SUM(.../...)*1.00 AS "Percent"
the 1.00 factor triggers the conversion from integer to 2-digit decimals.
Or may be SUM(...*1.00 / ...*1.00)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x

Villeroy
Volunteer

Posts: 26249
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Query SUM rounding to nearest whole number

Since your code already includes a multiplication of 100 . . . change it to 100.00 like below:

Code: Select all   Expand viewCollapse view
`SELECT "Q_FleetComposition"."FleetName",       "Q_FleetComposition"."Streamlining",       SUM( "Q_FleetComposition"."Fuel Capacity" * 100.00 / "Q_FleetTotals"."FleetFuelCap" ) AS "Percent"FROM "Q_FleetTotals",     "Q_FleetComposition"WHERE "Q_FleetTotals"."FleetName" = "Q_FleetComposition"."FleetName"GROUP BY "Q_FleetComposition"."FleetName",         "Q_FleetComposition"."Streamlining"ORDER BY "Q_FleetComposition"."FleetName" ASC,         "Q_FleetComposition"."Streamlining" ASC`

Explanation: Since the numbers you have defined are INTEGERS, it assumes you also want an INTEGER as the answer. You can multiply it by 1.00 for example to internally have it assign the result to two decimal places. Additionally, if you wanted to make it a number with an accuracy of up to 14 decimal places . . . multiply it by 1E0 ( or perhaps 100E0 in your case ) . By designating it a 1 followed by an E followed by the number 0 with NO spaces between the characters, this is a FLOAT number.

I hope this helps, please be sure to let me / us know.

Slideruel

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

Sliderule
Volunteer

Posts: 1160
Joined: Thu Nov 29, 2007 9:46 am

### Re: Query SUM rounding to nearest whole number

Yep, that worked, Just changed the "100" to "1.00" and got the decimal places I wanted.

& I can see the logic behind it, ta.

Cheers!
Matt

PS...
OpenOffice 3.3.0 on 32-bit Windows 7
OOBase, HSQLDB
Matt123

Posts: 35
Joined: Fri Apr 01, 2011 4:20 am