Looping through 2 different tables in mysql python

Question

I have this code:

query = "SELECT SUM(amount) FROM set_payment777 GROUP BY customer_VAT"
mycursor.execute(query)
for row0 in mycursor:
     query = "SELECT SUM(amount) FROM customers_payments777 GROUP BY customer_VAT"
     mycursor.execute(query)
     for row1 in mycursor:
         final_row = row0[0] - row1[0]

I have 2 tables in MySQL. They both store different amounts. I want to get the amounts from the table named "set_payment777" and subtract all its stored amount from the amounts stored in the table named customers_payments777.

For instance, we store the amount 150 in the table set_payment777 and the amount 25 in the other table. These 2 amounts are linked with the customer's VAT number. I can subtract the first set of numbers(150-25). The problem is when I have more than 1 customer. Let's say that the first customer has the VAT number 000000000 and the second customer the VAT number 111111111. If the first has the amounts 150 and 25 I can print 125 because 150-25 = 125. Now if the second customer has the amounts 200 and 50 it doesn't subtract 50 from 200 it subtracts 50 from 150 (the first customer's amount).

It is a little complicated but can someone find a solution?

Update the first query to also select the customer number. Update the second query to only select that customer. — John Gordon
– John Gordon, Commented Dec 18, 2021 at 13:15

VeteranSlayer · Accepted Answer · 2021-12-18 13:39:25Z

1

I think you should join the two tables and do the subtraction in the query

Something like this

SELECT (SUM(sp.amount) - SUM(cp.amount)) as final_row FROM set_payment777 sp 
JOIN customers_payments777 cp ON cp.customer_VAT = sp.customer_VAT
GROUP BY sp.customer_VAT

The whole code should be something like this

query = """SELECT (SUM(sp.amount) - SUM(cp.amount)) as final_row FROM set_payment777 sp 
JOIN customers_payments777 cp ON cp.customer_VAT = sp.customer_VAT
GROUP BY sp.customer_VAT"""
mycursor.execute(query)
for row0 in mycursor:
     #do whatever

I hope i helped

answered Dec 18, 2021 at 13:39

VeteranSlayer

4043 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Looping through 2 different tables in mysql python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related