CODEWITHSUNDEEP
sqlsqlite
Sheils
Sheils

Reputation: 343

How to return decimal numbers in sqlite calculated field

I have the following query:

SELECT
    qryFoodInCases.fldCaseID,
    qryFoodInCases.fldFood,
    AteAttackRate,
    NotAteAttackRate,
    Round( AteAttackRate / NotAteAttackRate, 2 ) RelativeRisk
FROM
    qryFoodInCases
    LEFT JOIN
    (
        SELECT
            qryNotAteInCase.fldCaseID,
            qryNotAteInCase.fldFood,
            COALESCE( qryNotAteAndGotSick.NotAteAndGotSick, 0 ) NotAteAndGotSick,
            qryNotAteInCase.NotAteInCase
            TotalNotAte,
            100 * COALESCE( qryNotAteAndGotSick.NotAteAndGotSick, 0 ) / qryNotAteInCase.NotAteInCase NotAteAttackRate
        FROM
            qryNotAteInCase
            LEFT JOIN qryNotAteAndGotSick ON qryNotAteInCase.fldFood = qryNotAteAndGotSick.fldFood
        GROUP  BY qryNotAteInCase.fldFood
    ) QA
        ON qryFoodInCases.fldFood = QA.fldFood

    LEFT JOIN
    (
        SELECT
            qryFoodInCases.fldCaseID,
            qryFoodInCases.fldFood,
            COALESCE( qryAteAndGotSick.AteAndGotSick, 0) AteAndGotSick,
            qryFoodInCases.fldFoodFrequency TotalAte,
            100 * COALESCE( qryAteAndGotSick.AteAndGotSick, 0 ) / qryFoodInCases.fldFoodFrequency AteAttackRate
        FROM
            qryFoodInCases
            LEFT JOIN qryAteAndGotSick ON qryFoodInCases.fldFood = qryAteAndGotSick.fldFood
        GROUP BY
            qryFoodInCases.fldFood
    ) QN
        ON qryFoodInCases.fldFood = QN.fldFood

GROUP BY
    qryFoodInCases.fldFood
ORDER BY
    AteAttackRate DESC,
    NotAteAttackRate

NB: Only the last field of the select statement is relevant to this question:

 round(AteAttackRate/NotAteAttackRate,2) RelativeRisk

That return the following records

+-----------+------------+---------------+------------------+--------------+
| fldCaseID |  fldFood   | AteAttackRate | NotAteAttackRate | RelativeRisk |
+-----------+------------+---------------+------------------+--------------+
|         1 | Beans      |           100 |               33 |            3 |
|         1 | Cabagge    |            66 |               75 |            0 |
|         1 | fried fish |            66 |               75 |            0 |
|         1 | Banana     |            50 |               80 |            0 |
|         1 | Pork       |            50 |              100 |            0 |
|         1 | Chicken    |            33 |              100 |            0 |
|         1 | Potatoes   |             0 |              100 |            0 |
|         1 | Rice       |             0 |              100 |            0 |
+-----------+------------+---------------+------------------+--------------+

What do I need to do to get the decimal values of the field RelativeRisk

Upvotes: 2

Views: 2416

Answers (1)

klin
klin

Reputation: 121604

Try:

select 
    ... 
    round(AteAttackRate/cast(NotAteAttackRate as float),2) RelativeRisk
    ...

Both AteAttackRate and NotAteAttackRate are integers, so AteAttackRate / NotAteAttackRate is integer too, and round(AteAttackRate / NotAteAttackRate, 2) changes nothing. You have to explicitly cast to float at least one of the numbers to obtain a float quotient.

Upvotes: 6

Related Questions