Selecting and subtracting all amounts from a mysql query-CodePudding

I have two tables in MySQL. The first stores a name, a customer VAT number, an amount, a date, and a variable called monthly. The other table stores the same things as the other table but without the variable called monthly. So my code is:

query = """SELECT sp.customer_surname, sp.amount, SUM(cp.amount), sp.monthly, sp.date_ 
FROM set_payment7777 sp 
INNER JOIN customers_payments7777 cp ON cp.customer_VAT = sp.customer_VAT
WHERE sp.date_ <= %s AND cp.date_ <= %s
GROUP BY sp.customer_VAT"""

mycursor.execute(query,(to_date,to_date,))
                    
for row in mycursor:
    if row[3].upper() == 'Y':
        final_amount = row[1] * ((to_date.year - row[4].year) * 12   (to_date.month - row[4].month))   row[1]
        final_subtraction = final_amount - row[2]
        file.write(f"{row[0]}\t{final_subtraction}\t")
        file.write("\n")
    else:
        final_subtraction = row[1] - row[2]
        file.write(f"{row[0]}\t{final_subtraction}\t")
        file.write("\n")

It is joining the two tables, getting all the amounts of the tables between two dates, and then if the monthly variable is set to 'Y' (it's an enum) it is multiplying the amount of the first table by the months that are between the two dates then subtracting them by the sum of the amounts of the other table. If the variable is set to 'N' it is doing the same but without multiplying the amount by the number of the months between the dates. My problem is that if I have more than one entry in the first table it only works with the first entry. For example, if I have the amounts 30 and 150 in the first table and the amounts 25, 50, 25 in the second table it will return 30-100 = -70. I could select the sum of the amounts of the first table but I don't want to because then I won't be able to check whether the variable is set to Y or N.

CodePudding user response：

It will probably work best with correlated query rather than join, something like this:

"""SELECT sp.customer_surname, sp.amount,
(Select SUM(cp.amount) from customers_payments7777 cp
where cp.customer_VAT = sp.customer_VAT AND sp.date_ <= %s AND cp.date_ <= %s
GROUP BY sp.customer_VAT) amountsum,
, sp.monthly, sp.date_ 
FROM set_payment7777 sp 
"""