mysql – Add elements of two tables

Question:

I am doing a query where I get the balance of the total of the VentaComision table and the total ComisionCobrada :

SELECT * FROM VentaComision;
Id Total
284 104.55
285 104.55 
SELECT * FROM ComisionCobrada;
id IDSaleCommission Total
72 284 50.00
73 284 54.55
74 285 4.47

When taking out the difference, the SUM(vc.Total) is adding 2 times the total of Id 284. The Total would have to be added only once.

SELECT (SUM(vc.Total) - SUM(cc.Total)) as saldo FROM VentaComision vc
LEFT JOIN ComisionCobrada cc ON cc.IdVentaComision = vc.Id
balance
204.63

Answer:

If you need to subtract the sum of the elements of one table from the sum of the elements of the other table, then this is the query you need:

SELECT
    SUM(Total) - (
      SELECT SUM(Total) FROM ComisionCobrada
    ) as saldo 
FROM VentaComision

The operation is simple: the sum of the totals of the VentaComision table is subtracted by the result of the subquery, which is the sum of the totals of the ComisionCobrada table.

You can see how it works at the following link:

If what you need is a list of each of the transactions, then it is the following:

SELECT
    Id,
    Total,
    Total - (
      SELECT SUM(Total) FROM ComisionCobrada cc
      WHERE vc.Id = cc.IdVentaComision
    ) as saldo
FROM VentaComision vc;

You can see the latter in the following link:

Post Views: 0
Scroll to Top