title: MySQL SUM function in multiple joins
body: <p>Hi so this is my case I have those tables</p>
  
  <pre><code>Customer {id,name}
  Charges {id,amount,customer_id}
  Taxes {id,amount,charge_id}
  </code></pre>
  
  <p>so I want to SUM amount of charges and taxes then group by customer id here is my query </p>
  
  <pre><code>SELECT SUM(ch.amount),SUM(t.amount)
  FROM Customer c
  LEFT JOIN Charges ch ON ch.customer_id = c.id
  LEFT JOIN Taxes t ON t.charge_id = ch.id
  GROUP BY c.id;
  </code></pre>
  
  <p>so in case I have 1 charge for customer than I have 2 taxes for that charge when I use SUM function it's counting amount of charge twice for example in case to show me 10$ it' showing me 20$</p>
  
  <p>I know how can I fix that through subqueries, but I want to know is there any option to get correct value without subqueries like query I use above what can I modify there to fix that.</p>
  
  <p>Thanks !</p>
  
  <p>UPDATED ANSWER WITHOUT SUBQUERIES</p>
  
  <pre><code>SELECT
    SUM(CASE WHEN @ch_id != ch.id
      THEN ch.amount END) AS ch_amount,
    SUM(t.amount)         AS t_sum,
    c.*,
    @ch_id := ch.id
  FROM
    Customer c
    LEFT JOIN charges ch ON c.id = ch.reservation_id
    LEFT JOIN taxes t ON ch.id = t.charge_id
  GROUP BY rs.id;
  </code></pre>