Adding 0 or null to missing fields

Question

I have the following tables:

student(sid, sname)

teacher(tid, tname)

enrollment(sid, cid, tid)

course(cid, course)

rank(sid, tid, grade, date, valid)

I need to calculate the average grade of all the teachers (data is in rank table), when only the grade from the most recent date counts (and if it's invalid - ignore it).

I wrote the following query, and it's working nice. The problem is that I also need the average for ALL the teachers, including those who were not ranked yet/their rank is invalid (their average grade will be 0 in that case, and I'll have to count their students like I did for the others). I think it's something with LEFT OUTER JOIN, but all the examples I see online have only two tables in FROM, and I can't figure out the right syntax in my case.

SELECT teacher.tid, 
       tname, 
       AVG(grade) AS avgGrade, 
       COUNT(DISTINCT enrollment.sid) AS studCount 
FROM rank, 
     teacher, 
     enrollment, 
     (   SELECT rank.sid, rank.tid, MAX(date) AS maxDate 
         FROM rank       
         GROUP BY sid, tid
     ) lastGrades 
WHERE teacher.tid=enrollment.tid 
  AND rank.tid=teacher.tid 
  AND rank.tid=lastGrades.tid 
  AND rank.sid=lastGrades.sid 
  AND rank.date=lastGrades.maxDate 
  AND valid = TRUE 
GROUP BY teacher.tid, tname

@Mihai, PostgreSQL has a COALESCE function, check this link: postgresql.org/docs/9.3/static/… — krokodilko
– krokodilko, Commented Dec 15, 2013 at 22:23
Do it with JOINS but use a LEFT JOIN for teachers so you get all teachers. — Mihai
– Mihai, Commented Dec 15, 2013 at 22:30
True, PostgreSQL does support COALESCE. Maybe I should try a different approach and use some form of UNION instead of LEFT OUTER JOIN? — Augustina
– Augustina, Commented Dec 16, 2013 at 8:37

Andomar · Accepted Answer · 2013-12-16 10:33:52Z

1

You could use a subquer to look up the latest rank per (teacher, student) combination. Use a left join to count enrollments that have not been ranked:

select  t.tid
,       t.tname
,       avg(r.grade) as AverageRank
,       count(distinct e.sid) as StudentCount
from    teacher t
join    enrollment e
on      t.tid = e.tid
left join
        rank r
on      r.tid = t.tid
        and r.sid = e.sid
        and r.valid = true
        and r.date =
        (
        select  max(date)
        from    rank r2
        where   r2.sid = r.sid
                and r2.tid = r.tid
                and r2.valid = true
        )
group by
        t.tid
,       t.tname

Example without data at SQL Fiddle.

The table design is kind of strange. You'd expect a student to enroll in a course, not in a teacher!

edited Dec 16, 2013 at 10:33

answered Dec 15, 2013 at 22:34

Andomar

239k55 gold badges387 silver badges412 bronze badges

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

2 Comments

Augustina Over a year ago

I wasn't able to try it, there's a syntax error somewhere. But I'm not familiar with "over" and "partition", hence not supposed to use them :)

Andomar Over a year ago

@Augustina: Updated the answer without row_number(). This one works on SQL Fiddle, so hopefully no syntax errors.

Collectives™ on Stack Overflow

Adding 0 or null to missing fields

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related