我可以在SQL中的聚合函数中包含非聚合列而不将其放入GROUP BY子句中吗？

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).

是的,有两个同名(Math)的课程可能在不同的时间。我想获得一个班级列表和每个班级注册的学生人数。我想要以下列(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

(注意我想GroupBy ClassId但不是ClassName)。这在SQLServer 2008中是否可行?显然我问,因为SQL抱怨

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

Thanks!

4 个解决方案

#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:

不,SQL Server不允许您省略GROUP BY中未包含在聚合函数中的列。包含类名称没有坏处,因为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

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

#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.

在GROUP BY语句中包含Classes.ClassName应该没有任何害处。您将通过不同的ClassId和ClassName对进行分组,因此(1,'Math')和(2,'Math')仍然是两个不同的分组。

#3

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

您可以将ClassName放在group by子句中,这样就可以了,因为它是ClassID的1到1:

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

SELECT Classes.ClassId,Classes.ClassName,Count(Registrations.RegistrationId)FROM Classes INNER JOIN 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.

或者在select子句中加上MAX(ClassName)。任何一个都会产生相同的结果。

#4

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

不,你不能:这是一个矛盾。

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

GROUP BY =折叠为离散值。如果不崩溃,则需要聚合它。

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

碰巧的是,无论如何你都会得到相同的结果,因为ClassName依赖于ClassID。

#1