SQL PHP HTML ASP JavaScript articles and free scripts to download
SQL

Calculating and storing SUM of a column in another table

Some time we have to collect the SUM , AVG or any other type of data from one table and store them in another table. Say for example we want to store sum of all marks of each student in a different table. We conducted different exams for students and now we will be adding all the marks for each student and store them in a different table. We will be using GROUP BY command to find out sum of all the marks or each student. Here is the SQL to do this.

insert into student3_total(s_id,mark) select id, sum(math + social +science) from student3 group by id

The above query will total all marks of each student and then store them in s_marks table. You can use AVG, MAX,MIN commands in place of SUM command to get desire result. Here is the one for AVG marks

insert into student3_total(s_id,mark) select id, AVG(math + social +science) from student3 group by id

Displaying the result with total mark using two tables

Above query will insert student id and sum of the mark to our student3_total table. Now we can join both table and display the final report. This report will collect name, id and individual mark from student3 table and total mark and student id from student3_total table. Two tables are joined by student id of both tables . Here is the query

SELECT id, name, class, social, science, math, s_id, mark FROM `student3` , student3_total WHERE id = s_id

Here are our both tables with records for you to test the quey.

student3: Download the SQL dump of the above student table

student3_total : Download the SQL dump of total of student mark

Be the first to post comment on this article :


Google+

Post Comment This is for short comments only. Use the forum for more discussions.
Name
Email( not to be displayed)Privacy Policy
1+2=This is to prevent automatic submission by spammers. Please enter the result of the sum as asked



Join Our Email List
Email:  
For Email Newsletters you can trust

HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

©2000-2014 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer