Geek Answers Handbook

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

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), because they can be at different times. I would like to get a list of classes and the number of students registered for each of them. I would like the following columns (ClassId, ClassName, StudentCount).

My attempt at this would be something like ...

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 a GroupBy ClassId, but not a 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."

Thank!

+5
source share
4 answers

, SQL Server GROUP BY, . , :

  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

, :

  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
+11

Classes.ClassName GROUP BY. ClassId ClassName, (1, 'Math') (2, 'Math') .

+5

ClassName group by, , 1-to1 ClassID:

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

MAX (ClassName) select. .

+2

, : .

GROUP BY = . , .

, , ClassName ClassID.

+1

More articles:


All Articles