Calculated field returning 0

[techguy]

I have a calculated field. I am trying to divide two queries. By themselves the work. But when I try to divide them to get a percentage it always returns 0

Calculated Field

(SELECT count(Director1) FROM shareholders WHERE Director1>0)    returns correct count

(SELECT count(Email) FROM shareholders)  returns correct count

 

(SELECT count(Director1) FROM shareholders WHERE Director1>0)/(SELECT count(Email) FROM shareholders)   ALWAYS ZERO

 

I have verified that the number has decimals set to 2, it will not calculate

 

Any suggestions to divide these? 

[Tubby]

34 minutes ago, techguy said:

I have a calculated field. I am trying to divide two queries. By themselves the work. But when I try to divide them to get a percentage it always returns 0

Calculated Field

(SELECT count(Director1) FROM shareholders WHERE Director1>0)    returns correct count

(SELECT count(Email) FROM shareholders)  returns correct count

 

(SELECT count(Director1) FROM shareholders WHERE Director1>0)/(SELECT count(Email) FROM shareholders)   ALWAYS ZERO

 

I have verified that the number has decimals set to 2, it will not calculate

 

Any suggestions to divide these? 

convert the values to FLOAT to get a value. The problem is because both are returning integers due to COUNT(). so it is actually computing right but is returning whole numbers only.

CONVERT(FLOAT, (SELECT count(Director1) FROM shareholders WHERE Director1>0))/CONVERT(FLOAT, (SELECT count(Email) FROM shareholders))

[Queso]

Hello, for more information on converting values to FLOAT using Convert(new data type, expression/value), please see this Function Reference on our Caspio HowTo article:  https://howto.caspio.com/function-reference/#:~:text=Miscellaneous Functions and Expressions

 

[autonumber]

Hi! Just to add, you may also format the result based on your preferred number of decimals or other formatting options  - https://howto.caspio.com/datapages/datapage-components/field-formatting-options/#:~:text=each field type.-,Customize Formatting Settings,-You can also