Group by a field not in select

group-by, sql

Solution

SELECT a.lecturerName, b.NumOfModules
FROM Lecturer a,(
SELECT l.lecturerID, COUNT(moduleID) AS NumOfModules
    FROM Lecturer l , Teaches t
    WHERE l.lecturerID = t.lecturerID
    AND year = 2011
    GROUP BY l.lecturerID) b
WHERE a.lecturerID = b.lecturerID

Problem

I want to find how many modules a lecturer taught in a specific year and want to select name of the lecturer and the number of modules for that lecturer. Problem is that because I am selecting `Name`, and I have to group it by name to make it work. But what if there are two lecturers with same name? Then sql will make them one and that would be wrong output. So what I really want to do is select `name` but group by `id`, which sql is not allowing me to do. Is there a way around it? Below are the tables: ``` Lecturer(lecturerID, lecturerName) Teaches(lecturerID, moduleID, year) ``` This is my query so far: ``` SELECT l.lecturerName, COUNT(moduleID) AS NumOfModules FROM Lecturer l , Teaches t WHERE l.lecturerID = t.lecturerID AND year = 2011 GROUP BY l.lecturerName --I want lectureID here, but it doesn't run if I do that ```

Original source