Android Question How to select in SQL

manuelsalazar · Dec 8, 2020

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 · Dec 8, 2020

Hello

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

Regards

John.

Bladimir Silva Toro · Dec 8, 2020

Hi @manuelsalazar

You must learn to use the JOIN clause

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

edgar_ortiz · Dec 9, 2020

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 · Dec 9, 2020

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

B4X:

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

Android Question How to select in SQL

manuelsalazar

Member

Jmu5667

Well-Known Member

Bladimir Silva Toro

Active Member

edgar_ortiz

Active Member

Albert Kallal

Active Member

Similar Threads

Android Question How to select in SQL

manuelsalazar

Member

Jmu5667

Well-Known Member

Bladimir Silva Toro

Active Member

edgar_ortiz

Active Member

Albert Kallal

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency