Sum columns in multiple tables - MySQL

How to sum columns in multiple tables in MySQL

We will see two methods to sum columns from multiple tables. The first method is more preferred than the second method. Therefore consider using first approach.

METHOD 1:

SELECT credit.contact_id, (IFNULL(credit, 0) - IFNULL(debit, 0)) as balance
     FROM (
    (SELECT SUM(credit) as credit, contact_id FROM table_a GROUP BY contact_id) as credit
       LEFT JOIN
    (SELECT SUM(debit) as debit, contact_id FROM table_b GROUP BY contact_id) as debit
     ON debit.contact_id = credit.contact_id);

METHOD 2:

SELECT contact_id, SUM(balance) FROM
    (SELECT contact_id ,credit as balance FROM table_a
       UNION ALL
    SELECT contact_id,- debit as balance FROM table_b
   ) as x
  GROUP BY contact_id;

Ads

Serplex® 180-IN-1 Precision Screwdriver Set with 3 Torque Modes and 162 Magnetic Precision Bits

XERGY LED Desk Lamp with USB Charging Output – Dimmable, Touch Control, Timer, Night Mode, 5 Color Modes