CODEWITHSUNDEEP
sqlpostgresqlquerying
StudioWorks
StudioWorks

Reputation: 493

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

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?

Upvotes: 0

Views: 220

Answers (2)

Jeremy
Jeremy

Reputation: 6723

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.

Upvotes: 2

Kiya
Kiya

Reputation: 168

You can use cross join or union

 SELECT total_presences from diary.period_attendance 
    where id_customer = 1492 and id_diary_period = 172818 and id_user = 835603 t1;
    CROSS APPLY 
    (SELECT t1.total_presences /count(*) 
    from diary.lesson 
    where id_diary_period = $1 and id_customer = $2 and end_date < now();
    ) t2;

Upvotes: 2

Related Questions