Friday, October 20, 2017

MySQL SUM function in multiple joins | MySQL JOIN with multiple tables and SUMS | Using SUM with multiple joins in mysql

MySQL SUM function in multiple joins | MySQL JOIN with multiple tables and SUMS | Using SUM with multiple joins in mysql


SELECT x1.id,SUM(x1.amount) as fake_total,COUNT(x1.id) as count,
(SELECT SUM(amount) FROM t1 WHERE t1.id=x1.id) as actual_total
FROM t1 x1 LEFT JOIN t2 x2 on x2.t1=x1.id
GROUP BY x1.id
ORDER BY x1.id asc

If you on "EXPLAIN" mode then you will get the below data:


EXPLAIN
SELECT x1.id,SUM(x1.amount) as fake_total,COUNT(x1.id) as count,
(SELECT SUM(amount) FROM t1 WHERE t1.id=x1.id) as actual_total
FROM t1 x1 LEFT JOIN t2 x2 on x2.t1=x1.id
GROUP BY x1.id
ORDER BY x1.id asc




Actually it will not take too much time to execute

No comments:

Post a Comment