CODEWITHSUNDEEP
sqlsql-servergroup-byaggregate-functions
Matthew Vanlandingham
Matthew Vanlandingham

Reputation: 646

SQL Server alternative to using subquery in aggregate function

I'm using an aggregate function to calculate a GPA for a particular student. I have three tables: COURSE, SECTION, and ENROLL. These tables are created as such:

create table COURSE
(
    Cno     varchar(9) primary key, 
    Cname   varchar(50),
    Credit  int check (Credit > 0)
);

create table SECTION
(
    Cno     varchar(9) REFERENCES COURSE(cno),
    Sno     varchar(9),
    Semester    varchar(15) check(Semester in('Fall','Spring','Summer')), 
    Year    int, 
    Sid     varchar(9) primary key 
);

create table ENROLL
(
    Mno     varchar(9) REFERENCES STUDENT(Mno),
    Sid     varchar(9) REFERENCES SECTION(Sid),
    Grade   CHAR check(Grade in('A','B','C','D','F')),
    primary key(Mno,Sid)
);

I have to convert the char Grade in the ENROLL table to a float value. The way to calculate the GPA in my case is:

SUM(grade of each course*credit hours of the course)/SUM(credit hours of each course)

Here is how I'm currently attempting to calculate the GPA:

 @grade =     
(
 (select SUM(CASE 
              WHEN Grade = 'A' THEN 4.0
              WHEN Grade = 'B' THEN 3.0
              WHEN Grade = 'C' THEN 2.0
              WHEN Grade = 'D' THEN 1.0
              WHEN Grade = 'F' THEN 0.0
             END
             * (
                select c.Credit from COURSE c where c.Cno = (
                 select s.Cno from SECTION s where s.Sid in (
                  select Sid from ENROLL where Mno = @mNum
                 )
                )
             )
         ) FROM ENROLL 
         where Mno = @mNum 
 ) / (
  select SUM(Credit) from COURSE c where c.Cno = (
   select s.Cno from SECTION s where s.Sid in (
    select Sid from ENROLL where Mno = @mNum
   )
  ) group by c.Cno
 )
)

There is an obvious issue, however. You cannot have a subquery inside of an aggregate function, so I have been stumped trying to find an alternative. Any help would be greatly appreciated.

Upvotes: 0

Views: 769

Answers (2)

George Joseph
George Joseph

Reputation: 5922

What i would try is to join the TABLES ENROLL and SECTION first.

This will give me all of the sections which are associated with Mno.

After that i will join with COURSE table to get all the courses associated with the Mno.

select * from enroll a join section b on a.Sid=b.Sid join course c on b.Cno=c.Cno

Then i will group by the a.Mno. Which means i am calculating the GPA per Mno based on the computation defined.

**SUM(grade of each course*credit hours of the course)/SUM(credit hours of each course)**

select a.Mno
       ,sum(case WHEN c.Grade = 'A' THEN 4.0
                 WHEN c.Grade = 'B' THEN 3.0
                 WHEN c.Grade = 'C' THEN 2.0
                 WHEN c.Grade = 'D' THEN 1.0
                 WHEN c.Grade = 'F' THEN 0.0
             END * c.Credit)/sum(c.credit) 
  from enroll a
  join section b
    on a.Sid=b.Sid
  join course c
    on b.Cno=c.Cno
group by a.Mno

Upvotes: 2

Salman Arshad
Salman Arshad

Reputation: 272146

Seems like you just need to JOIN the three tables together:

SELECT ENROLL.Mno, SUM(CASE
    WHEN Grade = 'A' THEN 4.0
    WHEN Grade = 'B' THEN 3.0
    WHEN Grade = 'C' THEN 2.0
    WHEN Grade = 'D' THEN 1.0
    WHEN Grade = 'F' THEN 0.0
END * COURSE.Credit) / SUM(COURSE.Credit)
FROM ENROLL
INNER JOIN SECTION ON ENROLL.Sid = Section.Sid
INNER JOIN COURSE ON SECTION.Cno = COURSE.Cno
GROUP BY ENROLL.Mno

Upvotes: 4

Related Questions