How to divide two queries to get a fraction in rsql?

Question

I am trying to find the fraction of the results generated by these two queries:

Numerator value:

dbGetQuery(db2, "SELECT COUNT(*) AS number_tweets
           FROM tweets JOIN users
           ON tweets.user_id_str = users.user_id_str
           WHERE text LIKE '%brexit%'
           AND users.screen_name_in = '1'")

Denominator value:

dbGetQuery(db2, "SELECT COUNT(*) AS number_tweets
           FROM tweets JOIN users
           ON tweets.user_id_str = users.user_id_str
           WHERE users.screen_name_in = '1'")

I have tried using subqueries but I always get 0 as the answer. Is there anything I'm doing wrong here:

dbGetQuery(db2, "SELECT x.number / y.number 
           FROM
           
           (SELECT COUNT(*) AS number
           FROM tweets JOIN users
           ON tweets.user_id_str = users.user_id_str
           WHERE text LIKE '%brexit%'
           AND users.screen_name_in = '1') x
           
           JOIN
           
           (SELECT COUNT(*) AS number
           FROM tweets JOIN users
           ON tweets.user_id_str = users.user_id_str
           WHERE users.screen_name_in = '1') y on 1=1
           
           ")

Tried this as well from comment, but get 0:

Answer 1

You should be able to do this in a single SELECT query by moving your WHERE condition into a SUM function:

SELECT SUM(IF(text LIKE '%brexit%', 1, 0)) / COUNT(*) AS myCalculation
FROM tweets JOIN users
ON tweets.user_id_str = users.user_id_str
WHERE users.screen_name_in = '1'

This adds up all the rows where text LIKE '%brexit%' and divides by the total rows.