Om.K Om.K - 16 days ago 5
SQL Question

Group by a field not in select

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

Answer
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
Comments