Can I include a non-aggregated Column in an aggregate function in SQL without putting it into a GROUP BY clause?

Question

Take the following tables...

Classes
ClassId ClassName
1       Math
2       Math
3       Science
4       Music

Registrations
RegistrationId ClassId StudentName
1              1       Stu
2              1       Rick
3              2       John
4              4       Barb
5              4       Dan
6              3       Einstein

Yes, there are 2 classes with the same name (Math) as they may be at different times. I would like to get a list of the classes and the number of students registered for each one. I would like the following columns (ClassId, ClassName, StudentCount).

My attempt at this would be something along the lines of...

SELECT Classes.ClassId, Classes.ClassName, Count(Registrations.RegistrationId)
FROM Classes
INNER JOIN Registrations ON Classes.ClassId = Registrations.ClassId
GROUP BY Classes.ClassId

(Note I would like to GroupBy the ClassId but NOT ClassName). Is this possible in SQLServer 2008? Obviously I ask because SQL complains

"ClassName is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Thanks!

Answer 1

No, SQL Server does not allow you to omit columns from the GROUP BY that are not wrapped in aggregate functions. There's no harm in including the class name, because the group by will be performed on the combination of the group by columns:

  SELECT c.classid, 
         c.classname, 
         COUNT(r.registrationid)
    FROM CLASSES c
    JOIN REGISTRATIONS r ON r.classid = c.classid
GROUP BY c.classid, c.classname

You could derive a table based on the counting, using:

  SELECT c.classid, 
         c.classname, 
         r.num
    FROM CLASSES c
    JOIN (SELECT t.classid,
                 COUNT(*) AS num
            FROM REGISTRATIONS t
        GROUP BY t.classid) r ON r.classid = c.classid

Answer 2

There should be no harm in including Classes.ClassName in your GROUP BY statement. You'd be grouping by the distinct pairs of ClassId and ClassName so (1, 'Math') and (2, 'Math') are still two distinct groupings.