I have written the following query and even though I used CAST if the
number is less then 1 it show up as 0. Here is the query:

select cast(count(SafetyTrainingYN) AS FLOAT)
from ICPCDATA
where SafetyTrainingYN = 1 /
(select count(SafetyTrainingYN)
from ICPCDATA)

Any help would be much appreciated.

Thanks,

jp


remove _nospam_ for my email

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Josh, is this what you're after?

SELECT
(SELECT CAST(COUNT(*) AS FLOAT)
FROM ICPCDATA
WHERE SafetyTrainingYN=1) /
(SELECT COUNT(SafetyTrainingYN)
FROM ICPCDATA)
AS PercentYes

In your query, you're looking for the count of rows where SafetyTrainingYN
is equal to the fraction 1/count(...) (zero when the table has more than 1
row due to integer division).

Hope that helps,
Rich


"Josh Phillips" <phillips3_nospam_@hotmail.com> wrote in message
news:40914d9e$0$203$75868355@news.frii.net...
> I have written the following query and even though I used CAST if the
> number is less then 1 it show up as 0. Here is the query:
>
> select cast(count(SafetyTrainingYN) AS FLOAT)
> from ICPCDATA
> where SafetyTrainingYN = 1 /
> (select count(SafetyTrainingYN)
> from ICPCDATA)
>
> Any help would be much appreciated.
>
> Thanks,
>
> jp
>
>
> remove _nospam_ for my email
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!