"You tried to execute a query that does not include the specified aggregate function"

Question

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?

Ethelred

You select SUM(orders.quantity) but you are joining author and book. Makes no sense to me. Am i missing something? – Srinagar

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

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.

Kiloliter

If you are to add all the selected fields into the grouped clause, doesn't that change number of results that are displayed since identical records resulting from any field would be grouped into a single field each time the group by clause groups by each of the specified fields? – Cellobiose

Not sure I understand what you're asking. If you SELECT fName, surname and then GROUP BY those fields, the query result set will include one row for each unique combination of fName and surname. Is there something else you would want instead? – Kiloliter

If there are two or more similar occurrences of fName, then this will be grouped into one row. Then the same will happen with surname, In the end you will have lesser rows. How can you avoid that? Am trying to avoid having lesser results coz of using group by – Cellobiose

But that is not the way it works. Even though Fred Smith and Fred Jones both have Fred as fName, the groups are based on the combination of fName and surname ... so that query result set will have separate rows (groups) for those two people. – Kiloliter

What if you had wanted to group using fName only but had to include surname on the group by clause to avoid the error addressed by this OP? Thanks for clarying. – Cellobiose

Then it would have to be just SELECT fName and GROUP BY fName If you wanted to also display a surname for the Fred group, which surname should it be ... Smith or Jones? You could put surname in an aggregate function in the SELECT clause ... Min(surname) or Max(surname) ... but in those cases you still would use just GROUP BY fName – Kiloliter

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

Killer

I don't understand what this means. On what basis were you summing if this were even allowed. What sql did this generate? – Bronwynbronx

Further to the comment from @Bronwynbronx - I just tried the suggested approach and it did not avoid the error. Please edit your answer to show a working example of the SQL statement generated by the Access query builder. (Switch to "SQL View", then copy and paste the SQL code into your answer.) – Foxtail

I have a screenshot that shows my experience very clearly, but I don't have enough reputation to post. – Killer

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

Alansen

Not really an answer to this question, is it? (BTW, to show the Totals row, just click the Totals icon in the query design toolbar.) – Dinse