How can divide two counted columns?

Question

I have the following code:

SELECT  (
        SELECT COUNT(distinct user_id)
        FROM   re_read
        ) AS re_user_id,
        
        (
        SELECT COUNT(distinct user_id)
        FROM   first_read
        ) AS first_user_id;

The result is:

re_user_id | first_user_id
--------------------------
     66231 |        210023    

But I would like to divide this two values and get re_user_id / first_user_id = 0,31535.

How should I do this in PostgreSQL?

Answer 1

There is useful thing named CTE.

Using it:

with t as (
    SELECT  (
            SELECT COUNT(distinct user_id)
            FROM   re_read
            )::numeric AS re_user_id,
            (
            SELECT COUNT(distinct user_id)
            FROM   first_read
            )::numeric AS first_user_id)
select re_user_id, first_user_id, re_user_id / first_user_id as division from t;

Note the type casting in the CTE query.