Why is this returning a zero?

# Thread: Why is this returning a zero?

1. Senior Member
Join Date
Dec 1969
Posts
292

## Why is this returning a zero?

I understand that whenever I make the first subtraction, then divide it by the total number again it will produce a less than zero value. But shouldn&#039;t the round function I have set up correct that?<BR><BR>ROUND((TotalSurveys - RemainingSurveys) / TotalSurveys, 2) * 100 AS PercentageCompleted

2. Senior Member
Join Date
Dec 1969
Posts
10,852

## RE: Why is this returning a zero?

Not when you&#039;ve got a 0 before you even get to the ROUND(), because the values are integers. Cast at least one of the values to a float or a real before you do the math, and you&#039;ll get a result that&#039;s more along the lines of what you&#039;re expecting.

3. Senior Member
Join Date
Dec 1969
Posts
292

## RE: Why is this returning a zero?

Ok, now i need a little help formatting it. This works:<BR><BR>ROUND(CAST((TotalSurveys - RemainingSurveys) AS DECIMAL) / TotalSurveys, 2) * 100 AS PercentageCompleted<BR><BR>But it&#039;s spitting out this:<BR>35.00000000000<BR><BR>It should just be 35.

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Maybe an easy way to fix it...

Depends on what DB you are using, but I will assume it is Access.<BR><BR>ROUND((1.0 * TotalSurveys - RemainingSurveys) / TotalSurveys, 2) * 100 AS PercentageCompleted<BR><BR>But you could also do that as<BR><BR>ROUND(100.0 - 100.0 * RemainingSurveys / TotalSurveys) AS PercentageCompleted

5. Senior Member
Join Date
Dec 1969
Posts
96,118

## Because you used DECIMAL...

...and so the ROUND gives you a DECIMAL and the multiply by 100 gives you DECIMAL. And so you get the default DECIMAL format, which is what you now see.<BR><BR>Try my<BR><BR>ROUND( 100.0 - 100.0 * RemainingSurveys / TotalSurveys ) AS PercentageCompleted<BR><BR>Actual format you see will still depend upon what DB you are using. You *might* need to do<BR><BR>CAST( ROUND( 100.0 - 100.0 * RemainingSurveys / TotalSurveys ) AS INTEGER ) AS PercentageCompleted<BR><BR>to ensure you get no decimal point.<BR>

6. Senior Member
Join Date
Dec 1969
Posts
292

## RE: Maybe an easy way to fix it...

all ways seem to be generating the same result set, but instead of displaying a whole number say like 35, it&#039;s generating 35.00000000000. i want to turn it into a whole number without any decimals once i get the answer.

7. Senior Member
Join Date
Dec 1969
Posts
292

## RE: Because you used DECIMAL...

thanks.

8. Senior Member
Join Date
Dec 1969
Posts
96,118

## How are you displaying this?

I gather you are *NOT* using any ASP page. Must be just dumping out a report?<BR><BR>Anyway, easy answer is to cast it to INTEGER.<BR><BR>Probably don&#039;t even need to ROUND, if you do that:<BR><BR>CAST( 100.0 - 100.0 * Remaining / Total AS INTEGER )<BR><BR>Probably CAST does an implicit rounding for you.<BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•