Answers.Body: <p>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 <a href="http://wiki.civicrm.org/confluence/display/CRMDOC/Create+a+Custom-Search+Extension" rel="nofollow noreferrer">http://wiki.civicrm.org/confluence/display/CRMDOC/Create+a+Custom-Search+Extension</a> .
  Then you have more control over the SQL and can join with a subquery on the "many" table that reduces it to "one".</p>
  
  <p>You might still be able to do it in a civireport, I just find it harder.</p>
  
  <p>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:
  <a href="https://stackoverflow.com/questions/3345657/mysql-how-do-i-sum-non-duplicates-values-when-doing-multiples-joins">https://stackoverflow.com/questions/3345657/mysql-how-do-i-sum-non-duplicates-values-when-doing-multiples-joins</a></p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-07-31T23:22:56.233Z
Answers.LastActivityDate: 2015-07-31T23:22:56.233Z
Answers.LastEditDate: 2017-05-23T12:38:59.763Z
Answers.UserDisplayName: Demerit
Body: <p>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 -</p>
  
  <ul>
  <li><a href="https://issues.civicrm.org/jira/browse/CRM-16338" rel="nofollow">https://issues.civicrm.org/jira/browse/CRM-16338</a> 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]</li>
  <li><a href="https://issues.civicrm.org/jira/browse/CRM-16719" rel="nofollow">https://issues.civicrm.org/jira/browse/CRM-16719</a> where SUM(duration) got doubled if the activity got two target contacts. 
  [civicrm_activity one-to-many civicrm_activity_contact]</li>
  </ul>
  
Tags: <civireport><mysql>
Title: What is the best way to avoid cartesian product on specific SELECT attributes?
ThreadText: # What is the best way to avoid cartesian product on specific SELECT attributes?
  
  (Asked by: username_0 on 2015-07-30)
  
  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]
  
  **Tags:** civireport, mysql
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2015-07-31. Score: 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>
  
  
  ---
  
  