Android Question How to select in SQL

[manuelsalazar]

Hi :

I have 2 tables.

table1 (invoices)
with fields : client, invoicedocument, invoiceamount

table2 (payments)
with fields: client, invoicedocument, payamount
(maybe 1 record, or more payments per invoicedocument)


i want a SELECT that shows

client, invoicedocument, invoiceamount, sum(payamount)

thanks in advanced
Manuel Salazar

 

[Jmu5667]

Hello

I suggest you look at this SQL Tutorial (w3schools.com)

Regards

John.

 

[Bladimir Silva Toro]

Hi @manuelsalazar

You must learn to use the JOIN clause

Link:https://www.w3schools.com/sql/sql_join_inner.asp

 

[edgar_ortiz]

You can try:

select client, invoicedocument, invoiceamount,
ifnull((select sum(payamount) from table2
where table2.client = table1.client and table2.invoicedocument = table1.invoicedocument),0) as total_payamount
from table1

 

[Albert Kallal]

ok this should work:
This assumes you want invoice document and the amounts EVEN if there are no payments.

SELECT Invoices.Client, InvoiceDocument, InvoiceAmount
SUM(payamount) as TotalPaid
FROM Invoices
LEFT JOIN Payments ON Payments.Client = Invoices.Client
GROUP BY Invoices.Client, InvoiceDocument, InvoiceAmount

Regards,
Albert D. Kallal
Edmonton, Alberta Canada