Union Select two queries subtract from particular data-CodePudding

I need to build a query which performs subtraction on basis of client name here is my query I built for getting data

SELECT invoice.client_name, (Sum(invoice.freight_rate) Sum(invoice.total_basic_amount) Sum(invoice.delivery_rate)) 
    FROM invoice GROUP BY invoice.client_name 
UNION SELECT client_name,(Sum(payments.payment_received)) 
    FROM payments GROUP BY client_name

And here is what I get as an output

client_name   |   Expr1001
John   |   2500
John   |   3630
MAc   |   12000
MAc   |   15300

What I need is

client_name   |   Expr1001
John   |   1130
MAc   |   3300

Which is simple subtraction of both query. I already asked this question before and I got some good responses but they aren't working and now nobody replies to it. Here is what I got from them.

SELECT invoice.client_name, (Sum(invoice.freight_rate) Sum(invoice.total_basic_amount) Sum(invoice.delivery_rate)-Sum(payments.payment_received)) AS Expr1
FROM invoice, payments
WHERE (([invoice].[client_name]=[payments].[client_name]))
GROUP BY invoice.client_name;

This query returns some very strange output which is

client_name   |   Expr1001
John   |   2260
MAc   |   18600

I'm Attaching the db file here is the link.

https://drive.google.com/file/d/1dbxHzXDbfe8l1ZDN9ZxZo7Rs1UImhV8r/view?usp=share_link

CodePudding user response：

You need subqueries:

Select 
    Charges.client_name, 
    TotalCharges-TotalPayments As Total

From

    (Select 
        invoice.client_name, 
        Sum(CCur([invoice].[freight_rate])) Sum(CCur([invoice].[total_basic_amount])) Sum(CCur([invoice].[delivery_rate])) As TotalCharges
    From 
        invoice
    Group By 
        invoice.client_name) As Charges

Inner Join

    (Select 
         payments.client_name, 
         Sum(CCur([payment_received])) As TotalPayments
    From 
         payments
    Group By 
         payments.client_name) As Payments

On Charges.client_name = Payments.client_name

and you should not store amount as text ...

CodePudding user response：

There are multiple invoice and payment records for each client. This means must aggregate each table's data then join those datasets.

SELECT I.client_name, amt - pmt AS Total
FROM (SELECT client_name, Sum(total_basic_amount) AS amt FROM invoice GROUP BY client_name) AS I 
LEFT JOIN (SELECT client_name, Sum(payment_received) AS pmt FROM payments GROUP BY client_name) AS P 
ON I.client_name = P.client_name;

Or this version:

SELECT invoice.client_name, Sum(total_basic_amount)-Max(pmt) AS Total 
FROM Invoice LEFT JOIN (SELECT client_name, Sum(payment_received) AS pmt FROM Payments GROUP BY client_name) AS P 
ON invoice.client_name = P.client_name 
GROUP BY invoice.client_name;