Skip to content
Advertisement

Correction Needed for error in Oracle query

I want calculate the all students credit hours separately group by stduents of each? needed help to correction of query

enter image description here

select distinct altcode,name,
(
SELECT sum(CRHRS)
      FROM V_ALLSTUDATA 
      WHERE grades in ('A','A+','B','B+','C','C+','D','D+')
      group by altcode
    ) as completed_credit_hours,
    (select sum(crhrs)
      from V_ALLSTUDATA
       where grades is null
        group by altcode
    ) as registerd_credit_hours
from V_ALLSTUDATA
where sem_code like'%FALL-19%'
      group by altcode,name;

Advertisement

Answer

Try this:

select distinct altcode,name,
(
SELECT sum(CRHRS)
      FROM V_ALLSTUDATA b
      WHERE grades in ('A','A+','B','B+','C','C+','D','D+') and a.name = b.name and a.altcode= b.altcode

    ) as completed_credit_hours,
    (select sum(crhrs)
      from V_ALLSTUDATA b
       where grades is null  and a.name = b.name and a.altcode= b.altcode

    ) as registerd_credit_hours
from V_ALLSTUDATA a
where sem_code like'%FALL-19%';
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement