search

progressivetech / net.ourpowerbase.sumfields

The Summary Fields extension creates read-only custom data fields that extend Contact and are automatically populated with up-to-date totals such as total lifetime contributions, last contribution amount, last attended event, etc.
Other
8 stars 29 forks source link

Total lifetime contributions causes crash - SQL error #71

Closed artfulrobot closed 4 years ago

artfulrobot commented 4 years ago

I'm seeing this db error:

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dmasterciv_yzj1n.trigger_table.contribution_id' which is not functionally dependent on columns in GROUP BY clause

The bit it's complaining about is:

SELECT contact_id, (SELECT IF(SUM(total_amount) IS NULL, 0, SUM(total_amount))

And I think this would fix it and be cleaner:

SELECT contact_id, MIN(SELECT SUM(COALESCE(total_amount,0)) ... )

I suspect this is a MySQL/MariaDB version thing. Previous versions I know reutrned a random value when there was no aggregate function and a field was not part of GROUP BY.

In theory, the values within the inner (SELECT) should all be the same (I haven't quite understood why they're being calculated multiple times which surely has a perfomance hit) and therefore any of them will do. I chose MIN().

artfulrobot commented 4 years ago

Closing as the master branch seems to work.