sql - Calculate ratio two tables

Question

Table1 has a "call" column and Table2 has a "pickup" column.

I want to find the call to pickup ratio but can't wrap my head around it.

I am using sqlite. I have tried this

SELECT (SELECT count (call)FROM table1)*100/ (SELECT count (pickup) FROM table2)

but I get a number and not a decimal back

question from:https://stackoverflow.com/questions/65858662/calculate-ratio-two-tables

Answer 1

SQLite does integer division. So, 1/2 is 0 rather than 0.5.

So, the simplest method is to use 100.0 instead of 100:

SELECT ((SELECT count(call) FROM table1) * 100.0 /
        (SELECT count(pickup) FROM table2)
       ) as call_to_pickup_ratio

This, in turn, runs the ricks of a divide by zero. The solution for that is NULLIF():

SELECT ((SELECT count(call) FROM table1) * 100.0 /
        (SELECT NULLIF(count(pickup), 0) FROM table2)

Finally, if call and pickup are never NULL, then you can just use COUNT(*):

SELECT ((SELECT count(*) FROM table1) * 100.0 /
        (SELECT NULLIF(count(*), 0) FROM table2)