Calculation field using division in SQLite3

Question

I have a sqlite3 table with 2 fields of INT. (Passed and Failed)

I want to calculate % Passed so am using the following SQL

SELECT id,
       passed,
       failed,
       passed / (passed + failed )* 100 AS 'passedPC'
  FROM myData

if Passed is 23 and Failed is 3 I am getting 0 returned which is unexpected.

Why is this?

Answer 1

It's because you are doing integer division and 23/26=0. You need to force it to do floating point division instead. The easiest way is to multiply one value with 1.0 like this:

SELECT id, passed, failed, ((passed * 1.0) / (passed + failed )) * 100 AS 'passedPC' 
FROM myData

Alternatively you could use the cast operator to force a a column to the real data type:

SELECT id, passed, failed, cast(passed as real) / (passed + failed ) * 100 AS 'passedPC' 
FROM myData;

Sample SQL Fiddle showing the difference.