How to combine these two queries from different tables into one to calculate percentage?

Question

I have the following query which has the students attendance in the period:

select total_presences from diary.period_attendance 
where id_customer = 1492 and id_diary_period = 172818 and id_user = 835603;

And I have the lesson count in the same period.

select count(*) from diary.lesson where id_diary_period = $1 and id_customer = $2 and end_date < now();

I'd like to divide total_presences by lessons count to get the students attendance percentage.

How do I do that in a single query?

Answer 1

Probably the easiest way is to use a CTE:

WITH lesson_count AS (
   select count(*) as lessons
   from diary.lesson 
   where id_diary_period = $1 and id_customer = $2 and end_date < now()
)
select total_presences, total_presences/lessons
from diary.period_attendance, lesson_count
where id_customer = 1492 
  and id_diary_period = 172818 
  and id_user = 835603;

Depending on the type of total_presences, you may have to cast it to numeric, real, or float to avoid integer math.