Count and Group By Query

[melissa820]

I need some help with creating a new count/ group by query.

This was my previous query which worked fine:

SELECT coursecode, section, term, year, count(*) AS enrollment FROM gradregister WHERE status='pending' GROUP BY coursecode, section ,term, year;

Here is the result:
| coursecode | section | term | year | enrollment |
+------------+---------+--------+------+------------+
| ACTG5P31 | 1 | Winter | 2005 | 37 |
| ACTG5P32 | 1 | Winter | 2005 | 33 |
| ACTG5P32 | 2 | Winter | 2005 | 20 |


The table GradRegister is a bunch of records for courses that students are taking, their marks etc.
There is another table called GradCourses which holds the records of course information.
I wanted to add in the course title from the GradCourses table to the previous query. I tried this but it didn't work:

SELECT r.coursecode, s.coursetitle, r.section, r.term, r.year, count(*) AS enrollment FROM gradregister r LEFT JOIN gradcourses s ON r.coursecode=s.coursecode WHERE r.status='pending' GROUP BY r.coursecode, r.section, r.term, r.year;

Which provided this result:

| coursecode | coursetitle . . . . . . . . . . . . . | section | term | year | enrollment |
+------------+-----------------------------+---------+--------+------+------------+
| ACTG5P31 | Advanced Management Accounting | 1 | Winter | 2005 | 37 |
| ACTG5P32 | Global Tax Management and Planning | 1 | Winter | 2005 | 66 |
| ACTG5P32 | Global Tax Management and Planning | 2 | Winter | 2005 | 40 |


Some of the enrolments are double of what they should be. Any ideas on what I am doing wrong?

[raf]

i don't understand why you need an outer join ... why not an inner join?

[melissa820]

I needed the left join just in case there are registration records that do not have corresponding course records. This can happen in our system.

Here is the query I ended up using:

SELECT 
a.coursecode, 
a.section, 
a.coursetitle, 
a.term, 
b.year, 
count(b.coursecode) AS enrollment 
FROM gradcourses a 
LEFT JOIN 
gradregister b 
ON a.coursecode=b.coursecode 
AND a.section=b.section 
AND a.term=b.term 
WHERE b.status='pending' 
GROUP BY 
a.coursecode, 
a.section, 
a.coursetitle, 
a.term, 
b.year 
ORDER BY 
a.term, 
b.year, 
a.coursecode, 
a.section

[guelphdad]

why post to the thead a year and a half later?

[melissa820]

Sorry I am not on these boards much anymore. A comment made on another thread asked me to provide the answers if I found them. So I went through some of my older posts and did that, just in case someone else had the same question later on.