Member Avatar for kyriakos70

Hello,
I have a master/detail table, in the detail I have a filed payment and another field sum_payment, all I want is when a new record is inserting the values of the field payment to displayed in the field sum_payment. I tried default value but nothing supports this, also tried query's in Mysql Manager but I get a lock timeout

SELECT 
  SUM(user_payments.payment) AS Sum_payment
FROM
  user_payments

I want to every new record that is inserting the value of sum_payment to increase as the ammount of the payment, I want the total sum of the fields payment to held in the field sum_payment or I have to create a new table with relation to the detail and a field which will hold the sum?

Thank you
Kyriakos

  • 2 Contributors
  • 1 Reply
  • 107 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by dickersonka
Member Avatar for dickersonka

normally you wouldn't store the sum of all the payments together like this

normally you have a total and payments

total
TOTAL_ID
TOTAL_AMOUNT

payments
PAYMENT_ID
TOTAL_ID
PAYMENT_AMOUNT

you might need to add users into this, but you normally don't want a column to be updated from the sum constantly, calculate each time you do a select

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.