I would like to create a view to display the number of nodes (later I'd like to also sum some numeric fields, but let's not run before we can walk).

In my view, I've turned on aggregation (under advanced settings), set the display type to fields, and added the title field with an aggregation type of "COUNT". However, in the view preview I'm getting the following error:

SQLSTATE[42000]: Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause: SELECT node_field_data.nid AS nid, COUNT(node_field_data.title) AS node_field_data_title FROM {node_field_data} node_field_data WHERE (( (node_field_data.status = :db_condition_placeholder_0) )) LIMIT 10 OFFSET 0; Array ( [:db_condition_placeholder_0] => 1 )

What do I need to do to fix this error?

2 Answers 2


Aggregation can be sort of a pain in Drupal 8's Views implementation. Part of this is due to limitations in how MySQL handles GROUP BY.

As @Jonathan points out, for an aggregation function to work at all, you need to have at least one group by expression. And if you want a function like COUNT or MIN to work as you expect, you only want the one group to be listed in the GROUP BY.

To get this to work right just now, you need to:

  • Under Advanced, you need to set Aggregation to "yes".
  • Under Format > Settings, you need to choose your Group by field.
  • You may not sort by anything other than the field you chose under Format > Settings, or your aggregation functions will give you bogus answers.

This is a bit limiting for some things you'll want to do, but as of 8.0.2, it's tricky to get better than this.

If anybody knows how to do better than this, I'd love to hear how :-)

  • Ah that makes sense, and is working for me now. I only want one result, but as I, I'm selecting a single content type in my filters, I can group by content type. Summing the entity fields is also working fine now. Thanks for your help. Commented Jan 26, 2016 at 10:02
  • Cant for my life find the "Aggregaton" settings under the advanced settings. Where is it? I see the issue with the SQL since views always include the primary key in the field list in the select query, even though I havent included it in the form. Annoying.
    – Johan
    Commented Mar 8, 2017 at 9:01
  • @Johan If still looking after 2 years, it's the fourth last link from the bottom. Commented Aug 22, 2019 at 20:40

Counts only make sense when you have a group to count. You need to add a field that you want to group by and set its aggregation setting to "Group similar results".

  • Happy to help James
    – Jonathan
    Commented Jan 26, 2016 at 11:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.