SQL query not returning expected values when summing total values in a column

Question

I have this schema:

Students (sid: int, firstName: str, lastName: str, yearStarted: int)
Majors (sid: int, major: str)
Note: a student may have more than one major.
Grades (sid: int, cid: int, credits: int, grade: int)
Note: sid, cid=foreign keys, grades: A=4, B=3, C=2, D=1, F=0.
Courses (cid: int, number: int, professor: str, major: str, year: int, semester: str) Note: cid is unique across semesters. Semester is either Summer, Fall, or Spring. Two course offerings are the same if they have same number + major

And with this schema I need to "Provide the SQL query that will generate the first name, last name, yearStarted, and the total number of credits for every student. You should not consider courses with a 0 grade, since these correspond to failed courses"

So far I have this:

def q4(self):
    query = '''
    select s.firstName, s.lastName, s.yearStarted,count(*)
    from students s, grades g
    where s.sid = g.sid
    and g.grade >0
    group by s.firstName, s.lastName, s.yearStarted
    '''
    self.cur.execute(query)
    all_rows = self.cur.fetchall()
    return all_rows

and returns these values:

[('Anne', 'Brown', 2020, 1), ('Jack', 'Thomson', 2018, 3), ('Jacob', 'McKarthy', 2020, 2), ('Jamal', 'Jones', 2019, 3), ('Jane', 'Doe', 2017, 2), ('John', 'Doe', 2017, 3), ('Tim', 'Burton', 2018, 3), ('Tina', 'Gilligan', 2019, 3)]

But apparently these are wrong, and when I upload to gradescope it gives me these errors located in the attached image

Any ideas what I am doing wrong?

Is there a reason you are choosing *not* to use proper, explicit, **standard**, readable `JOIN` syntax? — Gordon Linoff, Sep 19 '20 at 18:01
seems to me you have all the students but you have a different order than, does the exercise specify and order of the students? — Christian Sloper, Sep 19 '20 at 18:02
@GordonLinoff No, there is not. I HAVE used an inner join based on sid, and ended up with the sane errors. — overduekey, Sep 19 '20 at 18:02
@GordonLinoff this is obviously a student, maybe tone it down a bit? — Christian Sloper, Sep 19 '20 at 18:03
"and the total number of credits for every student" may be you need sum(g.credits) instead of count(*)? — terehpp, Sep 19 '20 at 18:10
@ChristianSloper So looking at my errors, it should be grouped by s.yearStarted desc? — overduekey, Sep 19 '20 at 18:15
@ChristianSloper got it! thank you so much for the hint, realized i just needed to group by id, yearStarted — overduekey, Sep 19 '20 at 18:22
No, it's not the `GROUP BY` that must be different, but the `ORDER BY`. It is strange though, they don't say which order they want in the description, but then tell you your result doesn't match, only because it is in another order than they expect. — Thorsten Kettner, Sep 19 '20 at 19:14
@ThorstenKettner yes sorry thats what i meant and what I did. And it is weird for the result, but I posted the answer that passed the automatic grader! — overduekey, Sep 19 '20 at 19:56

score 0 · Accepted Answer · answered Sep 19 '20 at 18:22

0

I have figured out the answer. Thank you to @ChristianSloper for helping me

def q4(self):
    query = '''
    select s.firstName, s.lastName, s.yearStarted,count(*)  as cnt
    from students s, grades g
    where s.sid = g.sid
    and g.grade >0
    group by s.sid, s.yearStarted
    order by s.yearStarted
    desc
     
    '''
    self.cur.execute(query)
    all_rows = self.cur.fetchall()
    return all_rows

answered Sep 19 '20 at 18:22

overduekey

51
1
7

2

Yep, it seems the only missing thing was the `ORDER BY`, which is not in the task description you have shown us, however. As firstname + lastname + yearstarted are probably unique in the table, you get the same result grouping by these or by the id (when grouping by the id, the yearstarted is superfluous in `GROUP BY`). You should not use comma-separated joins. They were used until explicit joins got invented - which was in 1992! If you are still taught these, this sheds a bad light on your class, book or tutorial. (One should know they exist, but we don't use them anymore.) – Thorsten Kettner Sep 19 '20 at 19:12
1

And be sure to always use proper, explicit, **standard**, readable `JOIN` syntax. – Parfait Sep 19 '20 at 20:19
@Parfait Agreed, I know its not the best way to do it. Will be using explicit joins from here on out. Thank you all for the advice! – overduekey Sep 20 '20 at 15:24

SQL query not returning expected values when summing total values in a column

1 Answers1