In some cases I see the 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" when creating the summary rows in a table. This is because we need to add a groupby clause when we add the aggregation fields.

Comments

I'm having the same on 7.x-1.0 version. it makes this module unusable :(

You may want to try turning on aggregation within Views. This gets rid of the error for me. It isn't calculating correctly but at least the error is gone :)

Please dustinrwh
Can you explain how you turn aggregation on.
I've been told the problem is solved also by turning off the MySQL mode "ONLY_FULL_GROUP_BY".
How can we do that (if it is a proper solution.
I got this error (in views using view_calc) after I transpoted my Drupal 7 site from test/development equipment to the production one. And it's still working properly on the test system.
Thanks in advance.
Pierre

+1 looking for a solution cause views calc is unusable with the MySQL mode "ONLY_FULL_GROUP_BY".

It's seem the problem is in the mysql request of the module...please help.

This is what I did to show the total of similar types of fields

  • Scenario: Say you have a node type with the fields 'material' and 'quantity' to input the quantity of each material you get, and you wish to show the sum of all material's quantity.
  • Preparation: go to 'admin/structure/views/settings' and enable 'show the SQL query'. This will show you the SQL query being used and help you debug if needed.
  • Solution:
    • Create a view
    • Make sure you don't have any sorting or filter on data that is unique per database entry, for example sorting by post date. If you do, delete that.
    • Enable 'Use aggregation' under advanced tab in views settings.
    • Add field: material
      • Aggregation settings:
        • Aggregation type: 'group similar results together'
        • Group column: 'value'
    • Add field: quantity
      • Aggregation Settings:
        • Aggregation type: sum
        • group column: value
    • Save and Done!

    Hope this helps someone.