Converting calculated field to real

I have a view with 3 integer fields: FailureCount, QuestionCount and
AuditCount. I want to get the following calculated result in a SELECT
statement:
SELECT FailureCount / (QuestionCount * AuditCount)
Problem is, this returns an integer result (as one would expect from 3
integers). I need the real number value, and CONVERT isn't working. I've
tried various syntaxes and I either get errors or no effect at all.
Any ideas how to properly do this?
Thanks,
Randall ArnoldOn Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>I have a view with 3 integer fields: FailureCount, QuestionCount and
>AuditCount. I want to get the following calculated result in a SELECT
>statement:
>SELECT FailureCount / (QuestionCount * AuditCount)
>Problem is, this returns an integer result (as one would expect from 3
>integers). I need the real number value, and CONVERT isn't working. I've
>tried various syntaxes and I either get errors or no effect at all.
>Any ideas how to properly do this?
>Thanks,
>Randall Arnold
>
Hi Randall,
One of many possibilities:
SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
Note that you have to convert at least one of the inputs to the division
instead of the result. Otherwise, integer logic is used for the division
and the result is then converted to real - but the fraction will already
be lost by then.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo! That's what I was looking for.
It turns out I was able to use a different method. In their original
queries, these are all counts in the Group By column of the designer, and I
just changed the select statement as follows:
SELECT CONVERT(real, COUNT(dbo.InternalAudit.DateInput)) AS AuditCount
I did the same for the other two fields, and everything worked. But I'll
hold onto your solution for cases where I'm stuck with integers I can't
change.
Randall Arnold
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:hl3vo1l5vbthugdgqqbef0q7c954njo4hk@.4ax.com...
> On Thu, 01 Dec 2005 23:50:07 GMT, Randall Arnold wrote:
>>I have a view with 3 integer fields: FailureCount, QuestionCount and
>>AuditCount. I want to get the following calculated result in a SELECT
>>statement:
>>SELECT FailureCount / (QuestionCount * AuditCount)
>>Problem is, this returns an integer result (as one would expect from 3
>>integers). I need the real number value, and CONVERT isn't working. I've
>>tried various syntaxes and I either get errors or no effect at all.
>>Any ideas how to properly do this?
>>Thanks,
>>Randall Arnold
> Hi Randall,
> One of many possibilities:
> SELECT CAST(FailureCount AS float) / (QuestionCount * AuditCount)
>
> Note that you have to convert at least one of the inputs to the division
> instead of the result. Otherwise, integer logic is used for the division
> and the result is then converted to real - but the fraction will already
> be lost by then.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)sqlsql