Turning on aggregration causes a SQL error

Question

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?

Answer 1

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

Answer 2

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