[Solved] Query SUM rounding to nearest whole number

Creating tables and queries

[Solved] Query SUM rounding to nearest whole number

Postby Matt123 » Tue Apr 05, 2011 7:50 am

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

Postby rudolfo » Tue Apr 05, 2011 2:01 pm

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

Postby Matt123 » Tue Apr 05, 2011 4:01 pm

:-) 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

Postby Villeroy » Tue Apr 05, 2011 4:13 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 26570
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Query SUM rounding to nearest whole number

Postby Sliderule » Tue Apr 05, 2011 4:38 pm

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 :super: ) . 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.
User avatar
Sliderule
Volunteer
 
Posts: 1166
Joined: Thu Nov 29, 2007 9:46 am

Re: Query SUM rounding to nearest whole number

Postby Matt123 » Wed Apr 06, 2011 1:41 am

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... :D
OpenOffice 3.3.0 on 32-bit Windows 7
OOBase, HSQLDB
Matt123
 
Posts: 35
Joined: Fri Apr 01, 2011 4:20 am


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 4 guests