how to combine 2 cte to get grouping-CodePudding

hi i need help combining 2 cte to get who get 100 attendance percentage but failed at exam

here my first cte

with main as(
select ca.STUDENT_ID,
    ca.SCHEDULE_ID,
    s.COURSE_ID,
    co.NAME as course_name,
    st.NAME,
    count(ca.ID) as total_attendance,
    ((CHAR_LENGTH(s.COURSE_DAYS) - CHAR_LENGTH(REPLACE(s.COURSE_DAYS , ',', ''))   1) * 13) as attendance_needed
from univ.course_attendance ca 
    left join univ.schedule s on ca.SCHEDULE_ID = s.ID
     left join univ.student st on ca.SCHEDULE_ID = st.ID 
     left join univ.course co  on ca.SCHEDULE_ID  = co.ID 
group by ca.STUDENT_ID, ca.SCHEDULE_ID
)
select *,total_attendance/attendance_needed as attendance_percentage
from main
order by 1,2;

second cte

;with inputdata as 
(
 select es.STUDENT_ID,es.EXAM_ID,es.SCORE,e.PASS_THRESHOLD, s.NAME , c.NAME as Course_name, es.EXAM_DT, 
    case 
     when SCORE>=PASS_THRESHOLD then 'PASS'
     else 'Fail'
    end as Flag
    from exam_submission es 
    left join student s  on es.STUDENT_ID  = s.ID
    left join exam e    on es.EXAM_ID  = e.ID 
    left join course c  on e.COURSE_ID  = c.ID  
)    
 select * from inputdata I
join 
 ( select student_id,exam_id from 
    inputdata 
    group by student_id, exam_id    
)T on I.student_id=T.student_id and I.exam_id=T.exam_id
order by exam_dt asc

result:

what i need student name, course name, attendace percentage & flag "failed/pass"

CodePudding user response：

Just chain multiple table expressions in a single CTE by introducing "aliases" like main_ordered for the first CTE and inputdata_grouped for the second one. I'm sticking with the original naming, but it could be improved.

with 
main as (
   select ca.STUDENT_ID,
      ...
   group by ca.STUDENT_ID, ca.SCHEDULE_ID),
main_ordered as (
   select *,total_attendance/attendance_needed as attendance_percentage
      ...
   order by 1,2),
inputdata as (
   select es.STUDENT_ID,es.EXAM_ID,es.S...
      ...),
inputdata_grouped as (
   select * from inputdata I
   ...
   group by student_id, exam_id...
   ...
   order by exam_dt asc)
select *
from main_ordered join inputdata_grouped on ...