MS-Access - 您尝试执行不包含指定聚合函数的查询

SELECT SUM(orders.quantity) AS num, fName, surname
FROM author
INNER JOIN book ON author.aID = book.authorID;

I keep getting the error message: "you tried to execute a query that does not include the specified expression "fName" as part of an aggregate function. What do I do?

我一直收到错误消息：“你试图执行一个不包含指定表达式”fName“的查询作为聚合函数的一部分。我该怎么办？

3 个解决方案

#1

The error is because fName is included in the SELECT list, but is not included in a GROUP BY clause and is not part of an aggregate function (Count(), Min(), Max(), Sum(), etc.)

该错误是因为fName包含在SELECT列表中，但不包含在GROUP BY子句中，并且不是聚合函数的一部分（Count（），Min（），Max（），Sum（）等）

You can fix that problem by including fName in a GROUP BY. But then you will face the same issue with surname. So put both in the GROUP BY:

您可以通过在GROUP BY中包含fName来解决该问题。但是那时你将面临与姓氏相同的问题。所以把它们都放在GROUP BY中：

SELECT
    fName,
    surname,
    Count(*) AS num_rows
FROM
    author
    INNER JOIN book
    ON author.aID = book.authorID;
GROUP BY
    fName,
    surname

Note I used Count(*) where you wanted SUM(orders.quantity). However, orders isn't included in the FROM section of your query, so you must include it before you can Sum() one of its fields.

注意我使用Count（*）你想要SUM（orders.quantity）。但是，订单不包含在查询的FROM部分中，因此您必须先包含它，然后才能Sum（）其中一个字段。

If you have Access available, build the query in the query designer. It can help you understand what features are possible and apply the correct Access SQL syntax.

如果您有Access可用，请在查询设计器中构建查询。它可以帮助您了解可能的功能并应用正确的Access SQL语法。

#2

I had a similar problem in a MS-Access query, and I solved it by changing my equivalent fName to an "Expression" (as opposed to "Group By" or "Sum"). So long as all of my fields were "Expression", the Access query builder did not require any Group By clause at the end. MS-Access - 您尝试执行不包含指定聚合函数的查询

我在MS-Access查询中遇到了类似的问题，我通过将我的等效fName更改为“Expression”（而不是“Group By”或“Sum”）来解决它。只要我的所有字段都是“Expression”，Access查询构建器最后就不需要任何Group By子句。

#3

GROUP BY can be selected from Total row in query design view in MS Access.
If Total row not shown in design view (as in my case). You can go to SQL View and add GROUP By fname etc. Then Total row will automatically show in design view.
You have to select as Expression in this row for calculated fields.

可以从MS Access中的查询设计视图中的总行中选择GROUP BY。如果在设计视图中未显示总行（如我的情况）。您可以转到SQL视图并添加GROUP By fname等。然后，Total行将自动显示在设计视图中。您必须在此行中选择“表达式”作为计算字段。

#1