CODEWITHSUNDEEP
sqloracle-databasesqlplus
Shreyas Kale
Shreyas Kale

Reputation: 65

SQL Expression missing error

I am trying to select from a table with the average of columns from other tables from other tables; The schema is as follows.

Students (sid, firstname, lastname, status, gpa, email)

Enrollments (sid, classid, lgrade)

Grades (lgrade, ngrade)

And the erroneous query is,

select sid, lastname, 
avg( select g.ngrade from grades g, enrollments e 
    where e.sid = sid and g.lgrade = e.lgrade and e.lgrade is not null 
    and g.ngrade is not null) as cgpa
from students
order by cgpa asc;

Upvotes: 0

Views: 295

Answers (3)

ErikE
ErikE

Reputation: 50201

There are several issues:

  • Remove the semicolon from the subquery.
  • You're attempting to use a correlated subquery as a value expression. That's fine, but you have to wrap it in parentheses to do so. I only see one set of parentheses, the ones for the avg() function.
  • You're also missing an alias on a column. For clarity in expressing your intent, you should be consistent and use aliases throughout.
  • Last, a GROUP BY clause is required somewhere.

All in all, I think the aggregate should be inside the parentheses.

Try:

select
   sid,
   lastname, 
   (
      select avg(g.ngrade)
      from grades g, enrollments e 
      where e.sid = sid and g.lgrade = e.lgrade
      and g.ngrade is not null
   ) as cgpa
from students
order by cgpa asc;

Other notes: e.lgrade is not null is not needed since the condition g.lgrade = e.lgrade already ensures it won't be null.

Last, I encourage you to learn ANSI join syntax. You'll thank me later. Seriously, using old-style joins is awful.

select
   s.sid,
   s.lastname, 
   (
      select avg(g.ngrade)
      from
         grades g
         inner join enrollments e
            on g.lgrade = e.lgrade
      where
         g.ngrade is not null
         and s.sid = g.sid
   ) as cgpa
from students s
order by cgpa asc;

In fact, I have a suspicion that simply rewriting the query this way will help expose what's wrong with it--it looks to me like maybe the grades and enrollments tables need another join condition?

Upvotes: 1

PM 77-1
PM 77-1

Reputation: 13334

Two INNER JOINs without sub-queries - please try.

SELECT s.sid, s.lastname, avg(g.ngrade) cgpa
FROM Students s
JOIN Enrollments e ON s.sid = e.sid
JOIN Grades g ON e.lgrade=g.lgrade
GROUP BY s.sid, s.lastname
ORDER BY 3;

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20794

Take the semi-colon out of your subquery.

Upvotes: 1

Related Questions