I am working in MySQL and I have to compare two columns ( Total
and PagoAbono
) from different tables, one called for example Historial
and another Pagos
. In the payments table there is the field Total
and in the history table I have a field called PagosAbono
.
In the table Pagos
there is only one "Folio" with a Total
and in the table Historial
there are several repeated "Folios" with PagosAbono
.
I have to add all the folios using SUM(PagosAbono)
and obtain a single Folio and its total. If the sum of those folios in the table Historial
is less than the total of the same folio in the table Pagos
(or they are not there), then I have to show them since I assume they still owe me.
I have this code sample, but it doesn't give me the result I'm looking for.
SELECT *
FROM Pagos A
INNER JOIN Historial B
ON ((SUM(B.PagosAbono) < A.Total) and (A.Folio = B.Folio))
How can I do it?
If I did not misunderstand your question, you could solve it with a subquery, that is, first of all we obtain the total of
Historial.PagosAbono
byFolio
and this query is the one that we will link withPagos
, by means of aLEFT JOIN
since it would be possible to have no records inHistorial
and I imagine that you would still like see them.You could have a query in
Pagos
with a column whose value will be the result of a subquery to add the payments made for the folio corresponding to that Payment.This query will return 3 fields: the folio, the total to pay for that folio, and the amount paid so far for that folio according to the History. Note that since this is
abonado
a computed/aggregated column, instead ofWHERE
usingHAVING
.You can see it working in this SQL Fiddle .