• 0

# Simple solution when table formula returns 0 instead of a decimal.

## Question

Hi

So this had me baffled yesterday and it took IT support to help me out. Once they responded and explained the problem I was able to find posts in the Caspio Community with SQL calculations returning 0 instead of decimal value, but up till then I just thought I was writing the maths wrong.

I'm just adding the issue and solution again for formula tables so maybe its easier for the next person to find (or myself when I forget When using all integers, Caspio will return an integer unless a field includes decimal value. In my example, I was trying to calculate the percentage of a plan the user had completed. Completed items had the value of 1 so my formula summed all them and divided by 7. Instead of returning a decimal every value was 0. All I had to do was change 7 to 7.00.  Other options to get a decimal easy could be to multiply a value by 1.00.  I could have also changed my 1s to 1.00 but I didn't realise that maths on integers didn't quite work the same as Excel!

## Recommended Posts

• 0

Hi @Jodie,

Thank you for that information!

I also have a workflow where I use a formula to pull values from my integer fields to calculate and put them into a number field. What I did was to use the Cast/ Convert SQL functions to automatically convert them to a Float data type that is compatible with the Number DataType of Caspio.

If anyone needs it, here are the two functions you can use:

CAST([@field:] as FLOAT)

CONVERT(FLOAT, [@field:])

##### Share on other sites

• 0

Hello!

In addition to the previous responses, you can also add .0 to the constant value.

For example, if you are going to divide the SUM of records into 10, you can use:

(SELECT SUM(FIELD) FROM TABLE) / 10.0

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible. ×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.