I want calculate the all students credit hours separately group by stduents of each? needed help to correction of query
x
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%';