Problems with SQL sum aggregate function

Question

I'm trying to run a query on a database that has 3 tables: COURSE, SECTION, ENROLL.

They are creating 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'm trying to run a SUM on all the credits for classes that a student is enrolled in. This is how I'm trying to accomplish this:

select 
    SUM(select Credit 
        from COURSE c 
        where c.Cno = (select s.Cno 
                       from SECTION s 
                       where s.Sid = (select Sid 
                                      from ENROLL 
                                      where Mno = @mNum));

However I'm getting an error in Visual Studio that says

Incorrect syntax near 'select' (the second select statement)

What is it that is wrong? Any help would be greatly appreciated.

Which dbms do you use?

Jens
– Jens

2018-12-03 05:36:43 +00:00
Commented Dec 3, 2018 at 5:36 — Jens
– Jens, Commented Dec 3, 2018 at 5:36

George Joseph · Accepted Answer · 2018-12-03 05:35:38Z

1

You would need an IN rather than an = if for example a Mno can enroll in multiple sections

select sum(Credit) 
   from COURSE c 
  where c.Cno in (select s.Cno 
                    from SECTION s 
                   where s.Sid in (select Sid 
                                    from ENROLL where Mno = @mNum)
                  )

answered Dec 3, 2018 at 5:35

George Joseph

5,93213 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Matthew Vanlandingham Over a year ago

Ah, you are very correct! That portion was overlooked and I have not tested my query yet! Thank you!!

Nikhil Over a year ago

if you are taking in you need to take group by c.Cno because there can be multiple c.Cno's

Nikhil Over a year ago

since all are primary keys c.Cno , s.Sid and Mno this query with in as well as = will work

George Joseph Over a year ago

@nikhil Have a look at the table structure of ENROLL table. It's the combination of (Mno,Did) which is a primary key. So that means single Mno can have multiple Sid and so the = will fail if there multiple sections a Mno is enrolled in

Nikhil Over a year ago

@GeorgeJoseph i also thought that at the first place thanks for clarification

Jens · Accepted Answer · 2018-12-03 05:35:34Z

1

try this:

select SUM((select Credit from COURSE c where c.Cno = 
(select s.Cno from SECTION s where s.Sid = (select Sid from ENROLL where Mno = @mNum)));

edited Dec 3, 2018 at 5:35

Jens

69.6k15 gold badges107 silver badges133 bronze badges

answered Dec 3, 2018 at 5:32

Ekha

223 bronze badges

2 Comments

Matthew Vanlandingham Over a year ago

Hi! The issue was not the parentheses, rather the way I was trying to use the aggregate function. My issue has been solved already! Thanks for answering though!

George Joseph Over a year ago

@Ekha it would be a good idea to test out the query using online db-fiddle or any of the online database test websites

Nikhil · Accepted Answer · 2018-12-03 08:17:52Z

1

see if it works:

select sum(Credit) from COURSE c where c.Cno in 
(select s.Cno from SECTION s where s.Sid in (select Sid from ENROLL where Mno = @mNum)) 
group by c.Cno;

edited Dec 3, 2018 at 8:17

answered Dec 3, 2018 at 5:32

Nikhil

3,9785 gold badges20 silver badges34 bronze badges

1 Comment

Matthew Vanlandingham Over a year ago

Thank you so much! Is the group by necessary though?

Collectives™ on Stack Overflow

Problems with SQL sum aggregate function

3 Answers 3

5 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related