What is the best way to avoid cartesian product on specific SELECT attributes?

Question

I faced similar issue in some CiviReports where some of the Select attributes applied with Group function like SUM(), got affected due to LEFT JOINs. Let me provide further clarification on basis of two resolved/current CiviCRM issues -

• https://issues.civicrm.org/jira/browse/CRM-16338 where contribution sum(total_amount) got tripled if the contribution is linked with three soft-credits. Although resolved it via splitting the query onto contribution and soft-credit queries respectively, which might not be the right way to do it :/ [civicrm_contribution one-to-many civicrm_contribution_soft]
• https://issues.civicrm.org/jira/browse/CRM-16719 where SUM(duration) got doubled if the activity got two target contacts. [civicrm_activity one-to-many civicrm_activity_contact]

Answer 1

CiviReport has a tradeoff between having a lot of functionality and nice widgets already done for you, but you have less control over the final SQL. When I run into this problem I usually do it as a custom search instead. See http://wiki.civicrm.org/confluence/display/CRMDOC/Create+a+Custom-Search+Extension . Then you have more control over the SQL and can join with a subquery on the "many" table that reduces it to "one".

You might still be able to do it in a civireport, I just find it harder.

If you're asking instead for general SQL help, then if I bing for "select join mysql sum duplicate" I get answers like this one: https://stackoverflow.com/questions/3345657/mysql-how-do-i-sum-non-duplicates-values-when-doing-multiples-joins