Use a calculated result of one request in another request

Sergey K 0
Viewed 17

I'm trying to calculate a metric with data coming from two independent tables:

SELECT COUNT(DISTINCT user_id) 
FROM users
--gives the count of users
SELECT SUM(costs)
FROM costs
--gives the total costs

How do I divide total costs/users?

postgresql
1 Answers

Did that like this:

   WITH costs
     AS (SELECT Sum(costs) AS budget,
                1          AS id
         FROM   costs),
     budget
     AS (SELECT COUNT(DISTINCT user_id) AS users_count,
                1                       AS id
         FROM   users)
SELECT budget / users_count AS Result
FROM   costs
       JOIN budget
         ON costs.id = budget.id

That is ugly, but does the job

Sergey K0
Related