Group by a field not in select

Question

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 1

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

Answer 2

You should probably just group by lecturerID and include it in the select column list. Otherwise, you're going to end up with two rows containing the same name with no way to distinguish between them.

You raise the problem of "wrong output" when grouping just by name but "undecipherable output" is just as big a problem. In other words, your desired output (grouping by ID but giving name):

lecturerName  Module
------------  ------
Bob Smith          1
Bob Smith          2

is no better than your erroneous output (grouping by, and giving, name):

lecturerName  Module
------------  ------
Bob Smith          3

since, while you now know that one of the lecturers taught two modules and the other taught one, you have no idea which is which.

The better output (grouping by ID and displaying both ID and name) would be:

lecturerId  lecturerName  Module
----------  ------------  ------
    314159  Bob Smith          1
    271828  Bob Smith          2

---

And, yes, I'm aware this doesn't answer your specific request but sometimes the right answer to "How do I do XYZZY?" is "Don't do XYZZY, it's a bad idea for these reasons ...".

Things like writing operating systems in COBOL, accounting packages in assembler, or anything in Pascal come to mind instantly :-)

Answer 3

You could subquery your count statement.

SELECT lecturername, 
       (SELECT Count(*) 
        FROM   teaches t 
        WHERE  t.lecturerid = l.lecturerid 
               AND t.year = 2011) AS NumOfModules 
FROM   lecturer l 

Note there are other ways of doing this. If you also wanted to elimiate the rows with no modules you can then try.

SELECT * 
FROM   (SELECT lecturername, 
               (SELECT Count(*) 
                FROM   teaches t 
                WHERE  t.lecturerid = l.lecturerid 
                       AND t.year = 2011) AS NumOfModules 
        FROM   lecturer l) AS temp 
WHERE  temp.numofmodules > 0