LEFT JOIN 3 tables with group by and get sum-CodePudding

I have 3 tables t_customer, t_order and t_payment.

t_customer:

customer_id	customer_name
1	May
2	Jerry

t_order:

order_id	customer_id	order_amount
1	1	12.00
2	1	20.00
3	2	15.00

t_payment:

payment_id	customer_id	pay_amount
1	1	15.00
2	1	12.00
3	2	12.00
4	2	3.00

How to write the sql to get the following result?

customer_id	customer_name	SUM(order_amount)	SUM(pay_amount)
1	May	32.00	27.00
2	Jerry	15.00	15.00

I tried to left join these 3 tables, but I got the following result.

customer_id	customer_name	order_amount	pay_amount
1	May	12.00	15.00
1	May	12.00	12.00
1	May	20.00	15.00
1	May	20.00	12.00

As you can see, if I group the result by custom_id and sum order_amount and pay_amount, the result will be 64.00 & 54.00.

Here's my sql:

select tc.customer_id, custom_name, SUM(order_amount), SUM(pay_amount)
from t_customer tc
left join t_order t on tc.customer_id = t.customer_id
left join t_payment tp on tp.customer_id = tc.customer_id
group by tc.customer_id

CodePudding user response：

The issue with your query is that when you combine t_customer with both t_order and t_payment, you get double combinations. Take a look at the output of this query to understand what I mean:

SELECT 
    c.customer_id,
    c.customer_name,
    o.order_amount,
    p.pay_amount
FROM      t_customer c
INNER JOIN t_order o ON c.customer_id = o.customer_id
INNER JOIN t_payment p ON c.customer_id = p.customer_id

In order to avoid this issue, you can move the aggregation operations before the join operations:

SELECT 
    c.customer_id,
    c.customer_name,
    COALESCE(o.total_order_amount, 0)   AS order_amount,
    COALESCE(p.total_pay_amount,   0)   AS pay_amount
FROM      
    t_customer c
LEFT JOIN (
    SELECT   customer_id,
             SUM(order_amount)  AS total_order_amount
    FROM     t_order
    GROUP BY customer_id
    ) o ON c.customer_id = o.customer_id
LEFT JOIN (
    SELECT   customer_id,
             SUM(pay_amount)    AS total_pay_amount
    FROM     t_payment
    GROUP BY customer_id
    ) p ON c.customer_id = p.customer_id

Try it here: https://www.db-fiddle.com/f/i9cn5JPZiiKTU91dVs2F8A/0.

CodePudding user response：

You could do a double join using a temporary table :

INSERT INTO t_final(customer_id,customer_name,order_amount) SELECT
    c.customer_id,
    c.customer_name,
    SUM(o.order_amount)
FROM      t_customer c
INNER JOIN t_order o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.customer_name;
UPDATE t_final SET pay_amount = (SELECT 
    SUM(p.pay_amount)
FROM t_payment p WHERE t_final.customer_id = p.customer_id GROUP BY t_final.customer_id LIMIT 1);
SELECT * FROM t_final;