Substracting from two tables in mysql-CodePudding

I Have two tables in mysql that store amounts. For instance, we have amounts0 and amounts1. Each table stores a name and an amount. Let's say that in amounts0 we have: "'James', 50". We also have "'Paul', 75". In the other table, we have: "'James', 25" and "'Paul' 50" and "'James', 10". I have a text file and I want a result like this:

What this is doing: It is collecting the amounts from the first table and subtracting them from the sum of the amounts in the second table grouped by name. For example in the picture above we have: James: 50 - sum(25, 10) = 25 - 35 so 15.

Can someone help me to create a query that does that with the 2 tables and then write them to the text file?

CodePudding user response：

I think you can use this

SELECT NAME,
       Abs(Sum(t1.amount) - t2.amount) AS amount
FROM   table1 AS t1
       JOIN (SELECT Sum( ` amount ` ) AS amount,
                    ` NAME ` as       t2name
             FROM   table2
             GROUP  BY ` NAME ` ) AS t2
         ON t2.t2name = t1.NAME
GROUP  BY NAME