CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-byaggregate
Justin
Justin

Reputation: 10897

Can I include a non-aggregated Column in an aggregate function in SQL without putting it into 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) 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!

Upvotes: 5

Views: 9927

Answers (4)

gbn
gbn

Reputation: 432271

No, you can't: it's a contradiction.

GROUP BY = collapse to discrete values. If you don't collapse, you need to aggregate it.

As it happens, you'd get the same result anyway because ClassName depends on ClassID.

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

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.

Upvotes: 5

Decker97
Decker97

Reputation: 1653

You can either put the ClassName in the group by clause, which will be ok because it is a 1-to1 with the ClassID:

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

or put a MAX(ClassName) in the select clause. Either one will yield the same result.

Upvotes: 2

OMG Ponies
OMG Ponies

Reputation: 332581

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

Upvotes: 11

Related Questions