calculated fields in sqldf

[matt.piet...@gmail.com]

Please excuse me if this has been addressed, I've searched the list, but did not find an answer to my question. This may also be my poor knowledge of SQL vocabulary. In any case, when I run the following:

"
SELECT Supplier_Name, Country, Total_KPI_Score,
(1 - (Total_Past_Due/Total_Non_Compliance_Items)) AS Compliance_Score
FROM srg_data
WHERE Compliance_Score <= 0.5 AND Total_KPI_Score >= 65
ORDER BY Total_KPI_Score DESC
"

My result returns only whole numbers for the calculated field "Compliance_Score".
If I run the same query in Access 2007, the query is executed and the calculated field contains floating values (e.g., 0.333).

I've had to calculate the field explicitly and add it to the data frame prior to running a query with sqldf as a work around. What am I missing?

Many thanks!

Matt

[Gabor Grothendieck]

sqlite does integer division of integers (unlike R which does real division of integers).   For example,

> sqldf("select 2/3")

  2/3

1   0

Cast the numerator or denominator to real or multiply by 1.0 to convert to real::

> sqldf("select cast(2 as real) / 3")

  cast(2 as real) / 3

1           0.6666667

> sqldf("select 1.0 * 2 / 3")

  1.0 * 2 / 3

1   0.6666667