[Solved] Query SUM rounding to nearest whole number

[Matt123]

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.

[rudolfo]

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" ?

[Matt123]

ok

Code: Select all

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

[Villeroy]

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)

[Sliderule]

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

Code: Select all

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.

[Matt123]

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...