Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Auto Sum an integer field column to another field

Posted on 2008-11-17
9
Medium Priority
?
1,046 Views
Last Modified: 2013-12-13
Hello,
I have a MySQL database with 2 tables the second has 5 fields username password id(primary key) payment(int) sum(int), what I want in the field sum to auto insert the sum of the field payment when a new record is inserted eg recordcount 1 username=kjjk etc payment=100 sum=(100), 2 payment=100 sum=200(100+(recordcount2)100), I have found about the select and sum but it has nothing to do with the second field and I don't know hwere to use it in a php form or in MySQL.

Kyriakos
0
Comment
Question by:kyriakos70
  • 5
  • 4
9 Comments
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22978637
Two options:

1) Calculate it every time you need it.

2) Use triggers to update the column each time you add, delete or update the other table.

Option 1 is easiest, option 2 requires mysql5

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

For option 1, you would use table strucure and queries like this:
# user: userid, username, password
# payments: userid, paydate, amount
 
select userid, username, sum(paymeny.amount) 
from user
left join payment on 
  payment.userid = user.userid
group by userid, username

Open in new window

0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22978663
Sorry, the query is wrong, because two columns are named 'userid' they must be prefixed with the alias:
select user.userid, username, sum(paymeny.amount) 
from user
left join payment on 
  payment.userid = user.userid
group by user.userid, username

Open in new window

0
 

Author Comment

by:kyriakos70
ID: 22979481
Can you give me an example code of the trigger, on how to sum a column to another column of the same table? I get an error on trigger. Or give me a hint on a query from the trigger below the <sum_player_pay> and <Player_pay> are fields of the same table or shall I have to create a new Table and add the sum to a field on the new table?
CREATE DEFINER = 'root'@'localhost' TRIGGER `user_payments_after_ins_tr` AFTER INSERT ON `user_payments`
FOR EACH ROW
BEGIN
INSERT INTO `user_payments` SET Sum_Player_pay = SUM(Player_pay);
END;

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22980216
Please read more about triggers. It is important that you understand how they work, if you are going to use them. If you are a new to databases, you should probably go for option 1...

To be able to help you with details, you have to show your database schema. Execute

SHOW CREATE TABLE user_payments;

and the same for the other table you have, and post the output here.
0
 

Author Comment

by:kyriakos70
ID: 22980462
OK,
I will go to option1, thank you :
# Structure for the `user_details` table :
#

CREATE TABLE `user_details` (
  `id` INTEGER(6) NOT NULL AUTO_INCREMENT,
  `Name` CHAR(12) COLLATE greek_general_ci DEFAULT NULL,
  `Surname` CHAR(20) COLLATE greek_general_ci DEFAULT NULL,
  `E-mail` CHAR(20) COLLATE greek_general_ci DEFAULT NULL,
  `Username` CHAR(20) COLLATE greek_general_ci DEFAULT NULL,
  `Password` INTEGER(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
)ENGINE=InnoDB
AUTO_INCREMENT=2 CHARACTER SET 'greek' COLLATE 'greek_general_ci'

Structure for the `user_payments` table :
CREATE TABLE `user_payments` (
  `id_2` INTEGER(11) NOT NULL AUTO_INCREMENT,
  `id` INTEGER(11) NOT NULL,
  `date` DATE DEFAULT NULL,
  `Player_pay` INTEGER(6) NOT NULL,
  `Sum_Player_pay` INTEGER(11) DEFAULT NULL,
  PRIMARY KEY (`id_2`),
  UNIQUE KEY `id_2` (`id_2`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `id_3` (`id`),
  CONSTRAINT `id` FOREIGN KEY (`id`) REFERENCES `user_details` (`id`)
)ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci'
0
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 2000 total points
ID: 22980579
When you need a list of users with the sum of their payments, use a query like below. I added first payment, last payment, payment average, smallest/largest payment and number of payments. Just remove those you do not need.

You can add a where clause. For instance, to get this information for only one user, add WHERE id=<id>

The Sum_Player_pay column is not needed.
SELECT u.id,Name,Surname,`E-mail`,Username,Password,
  SUM(p.Player_pay) AS 'Total_payment',
  MIN(p.date) AS 'First_payment',
  MAX(p.date) AS 'Last_payment',
  AVG(p.Player_pay) AS 'Average_payment',
  MIN(p.Player_pay) AS 'Smallest_payment',
  MAX(p.Player_pay) AS 'Largest_payment',
  count(p.id_2) AS 'Payment_count'
FROM user_details u 
LEFT JOIN user_payments p ON
  p.id=u.id
GROUP BY 1,2,3,4,5,6 # refers to the 6 'normal' fields in the select

Open in new window

0
 

Author Comment

by:kyriakos70
ID: 22983097
I will accept your solution thanks this is what I am looking for only one thing where do I put this query in a new query of the database? or in a php code, I want it to be executed automaticly.

Kyriakos
0
 

Author Closing Comment

by:kyriakos70
ID: 31517548
Thank you what I needed
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22991020
Put it where you need it...? What do you mean you want it executed automatically? It does not update anything, it calculates the current total payment for each player.

You could put it in the database, storing it as a view. That would make it easy to use when you need it:

http://dev.mysql.com/doc/refman/5.1/en/create-view.html
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to implement server side field validation and display customized error messages to the client.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question