CODEWITHSUNDEEP
sqloracle-databasejoin
SaSquadge
SaSquadge

Reputation: 219

Row that should have a 0 count not showing

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

Upvotes: 1

Views: 106

Answers (3)

Linger
Linger

Reputation: 15058

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;

Upvotes: 2

Lord Peter
Lord Peter

Reputation: 3501

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...

Upvotes: 0

Maksim Sirotkin
Maksim Sirotkin

Reputation: 473

@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.

Upvotes: 0

Related Questions