Row that should have a 0 count not showing

Question

I'm working through a problem, working with SQL Oracle. I'm getting the right results beside a row that should be showing up that has a count of 0. The question is

Question:

For each section of the Project Management course, list the section ID, location and number of students enrolled. Sort by section ID.

My Code:

SELECT s.Section_Id, s.Location, COUNT(*) AS Enrolled
FROM Course c, Section s,  Enrollment e
WHERE c.Course_No = s.Course_No
  AND s.Section_Id = e.Section_Id
  AND c.Description = 'Project Management'
GROUP BY  c.Course_No, s.Location, s.Section_Id
ORDER BY s.Section_Id;

My Results:

SECTION_ID    LOCATION                ENROLLED
----------    -------------------     ----------
48            L211                    4 
119           L211                    3 
120           L214                    2

Expected Results:

SECTION_ID    LOCATION                ENROLLED
----------    -------------------     ----------
48            L211                    4
119           L211                    3
120           L214                    2
121           L507                    0

So as you can see I'm missing the row with 0 enrolled on my results and can't seem to get that row to appear. Also you will notice that it is a section id and location that go with it for that project management class but it won't appear. I'm not sure what I'm doing wrong.

Any help would be great, also here is the Schema.

DBMS: I'm using Oracle SQL Developer

You are going to have to do a left join to enrollment. Then you'll need to use either nvl() or coalesce to replace the null values with 0. — Dan Bracuk, Jul 28 '14 at 14:49

score 2 · Accepted Answer · edited Jul 28 '14 at 15:20

2

How about joining to the tables instead of using the WHERE clause:

SELECT s.Section_Id, 
       s.Location, 
       COUNT(e.Section_Id) AS Enrolled 
FROM   course c 
       LEFT JOIN section s 
              ON c.Course_No = s.Course_No 
       LEFT JOIN enrollment e 
              ON s.Section_Id = e.Section_Id 
WHERE  c.Description = 'Project Management' 
GROUP  BY c.Course_No, 
          s.Location, 
          s.Section_Id 
ORDER  BY s.Section_Id;

edited Jul 28 '14 at 15:20

Kermit

33,827
13
85
121

answered Jul 28 '14 at 14:46

Linger

14,942
23
52
79

That is showing the row now but for some reason show enrolled as 1? – SaSquadge Jul 28 '14 at 14:50
@OmniSquad, I modified my answer – Linger Jul 28 '14 at 14:52
Okay I got it with this answer I added Student_Id into the count and took out c.Course_No in group by and it worked! Also your updated answer worked ! – SaSquadge Jul 28 '14 at 14:57

Lord Peter · Answer 2 · 2014-07-28T15:01:31.097

0

You need to outer join to course and enrollment otherwise you won't see sections that don't have any courses/enrollments. You can either use the ANSI syntax ...LEFT JOIN... etc or the old Oracle syntax using (+) against the columns of the deficient table:-

SELECT s.Section_Id, s.Location, COUNT(*) AS Enrolled
FROM Course c, Section s,  Enrollment e
WHERE c.Course_No (+) = s.Course_No
  AND s.Section_Id = e.Section_Id (+)
  AND c.Description (+) = 'Project Management'
GROUP BY  c.Course_No, s.Location, s.Section_Id
ORDER BY s.Section_Id;

These days I would use the ANSI syntax...

edited Jul 28 '14 at 15:01

answered Jul 28 '14 at 14:51

Lord Peter

3,433
2
33
33

2

Oracle discourages the use of the old style, proprietary `(+)` syntax for outer joins. You should really start using the explicit `JOIN` syntax. – Jul 28 '14 at 14:53
Non-ANSI joins should be used for backward compatibility, not for new development ([related question/answer](http://stackoverflow.com/a/1193722/335858)). – Sergey Kalinichenko Jul 28 '14 at 14:53

score 0 · Answer 3 · answered Jul 28 '14 at 14:56

0

@OmniSquad you could use SQL provided by Linger to create LEFT OUTER JOIN and select records where is no enrolment and changes statement COUNT(e.Section_Id) AS Enrolled.

answered Jul 28 '14 at 14:56

Maksim Sirotkin

473
2
5
14

Row that should have a 0 count not showing

3 Answers3

Linked

Related