MYSQL to group tables and apply aggregate subtraction

Question

tablo1
--------------------------------------
firma---------------------alacak
Firma1-------------------500
Firma1-------------------300
Firma1-------------------700
Firma2-------------------400
Firma2-------------------200

tablo2
--------------------------------------
firma---------------------tahsilat
Firma1-------------------300
Firma1-------------------200
Firma2-------------------100

I have 2 tables. I want to group and aggregate the "firma" in table 1 and table 2. I want to get the results out.

("tablo1" Firma1 -> 1500) - ("tablo2" Firma1 -> 500) = 1200
("tablo1" Firma2 -> 600) - ("tablo2" Firma2 -> 100) = 500

The result I want to print:
firma---------alacak
Firma1-------1200
Firma2-------500

I tried a code like this:

SELECT tablo1.firma,
       (COALESCE(tablo1.alacak) - COALESCE(tablo2.tahsilat)) AS sonuc
FROM (SELECT firma, SUM(alacak) AS alacak
      FROM tablo1
      GROUP BY firma
     ) AS tablo1 LEFT JOIN
     (SELECT firma, SUM(tahsilat) AS tahsilat
      FROM tablo2
      GROUP BY firma
     ) AS tablo2
     ON tablo1.firma = tablo2.tahsilat
ORDER BY tablo1.firma ASC

Gordon Linoff Gordon Linoff · Accepted Answer · 2017-12-03T16:38:47

Your code would work with the right join condition:

ON tablo1.firma = tablo2.firma

However, in MySQL, I prefer using union all and group by:

select firma, sum(amount)
from ((select firma, alacak as amount
       from tablo1
      ) union all
      (select firma, - tahsilat
       from tablo2
      )
     ) tt
group by firma;

This works even if records are in only one table, regardless of the table they are in.

MYSQL to group tables and apply aggregate subtraction

1 Answers