I need some help with creating a new count/ group by query. The results of this query will be parsed with php to output on a web page.
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?
