Subtract data from two table to get total-CodePudding

I need to find out total pending payment of customer. I have two tables: 1 is for invoices and 2nd is for received payments.

Here is the schema of invoice table

inv_no  |  date |  customer_name | total_baic | freight_rate | delivery_rate

and here is the schema of payment table

id | date | customer_name | payment_received | inv_no

What I need is pending customer wise like customer_name, total pending where total pending is (total_basic freight_rate delivery_rate) - (payment_received)

What I have tried is getting both differently but don't know how to do this subtraction part

Here is the query of pending from invoice table

SELECT Sum(invoice.freight_rate)   sum(invoice.total_basic_amount)   sum(invoice.delivery_rate)   sum(invoice.advanced_amount) AS totalpending FROM invoice GROUP BY invoice.client_name

Here is the query of payment recevide from payments table

SELECT Sum(payments.payment_received) AS totalreceived FROM payments GROUP BY payments.client_name

How to do substration of both like totalpending - totalreceived.

Somehow I'm able to create this query but problem is this query return double amount please can you chekc

**SELECT invoice.client_name, Sum(invoice.freight_rate) Sum(invoice.total_basic_amount) Sum(invoice.delivery_rate) AS totalpending, Sum(payments.payment_received) AS totalpayment FROM invoice, payments GROUP BY invoice.client_name, payments.client_name**

I'm getting

client_name | totalpending | totalpayment
John | 1800 | 1100

Here total payment is correct coz in my payment table there is two entry one is for 600 and another for 500 so it's 1100 but in invoice table there is only one entry where total basic = 600 and freight rate =0 deliverrate also 0 so here totalpending has to be 600

CodePudding user response：

From what I understand, this might be the solution you are looking for:

select invoice.customer_name, (sum(invoice.freight_rate)   sum(invoice.total_basic_amount)  sum(invoice.delivery_rate) - sum(payments.payment_received)) as TotalPending
from invoice,payments where invoice.inv_no=payments.inv_no

This query will get you the customer names and their total pending payments.