MySQL SUM function in multiple joins

Question

Hi so this is my case I have those tables

Customer {id,name}
Charges {id,amount,customer_id}
Taxes {id,amount,charge_id}

so I want to SUM amount of charges and taxes then group by customer id here is my query

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;

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$

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.

Thanks !

UPDATED ANSWER WITHOUT SUBQUERIES

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;

O. Jones · Accepted Answer · 2016-10-29 21:58:49Z

You want to know if you can do this without subqueries. No, you can't.

If a row in Charges has more than one corresponding row in Taxes, you can't simply join the tables without duplicating Charges rows. Then, as you have discovered, when you sum them up, you'll get multiple copies.

You need a way to get a virtual table (a subquery) with one row for each Charge.

                    SELECT ch.customer_id,
                           ch.amount amount,
                           tx.tax tax
                      FROM Charges
                      LEFT JOIN (  
                                  SELECT SUM(amount) tax,
                                         charge_id 
                                    FROM Taxes
                                   GROUP BY charge_id
                          ) tx ON ch.id = tx.charge_id

You can then join that subquery to your Customer table to summarize sales by customer.

Gordon Linoff · Accepted Answer · 2016-10-29 22:36:15Z

1

This is a pain because of the multiple hierarchies. I would suggest:

SELECT c.id, ch.charge_amount, ch.taxes_amount
FROM Customer c LEFT JOIN
     (SELECT ch.customer_id, SUM(ch.amount) as charge_amount,
             SUM(t.taxes_amount) as taxes_amount
      FROM Charges ch LEFT JOIN
           (SELECT t.charge_id, SUM(t.amounts) as taxes_amount
            FROM taxes t
            GROUP BY t.charge_id
           ) t
           ON t.charge_id = ch.id
      GROUP BY ch.customer_id
     ) ch
     ON ch.customer_id = c.id;

You are not going to be able to fix this without subqueries of one form or another, if there are multiple charges for a customer or multiple taxes on a charge.

answered Oct 29, 2016 at 22:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

MySQL SUM function in multiple joins

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related