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

wsadowicz’s picture

wsadowicz commented

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

dustinrwh’s picture

dustinrwh commented

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

pyvarlet’s picture

pyvarlet commented

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

fboulanger’s picture

fboulanger
Primary language French
commented

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

webcube’s picture

webcube commented

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.

windsochi’s picture

windsochi commented
Issue summary: View changes

Hello.
I too 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.

Please need help!