"You tried to execute a query that does not include the specified aggregate function"
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.)
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
:
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.
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.
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.