Inserting SUM of a column from one table to other

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 student3_total table. We can use AVG, MAX,MIN commands in place of SUM command to get desire result.
Storing Average Mark

INSERT INTO student3_total(s_id,mark) SELECT id, AVG(math + social +science) from student3 GROUP BY id

Creating a table & storing data.

If we have not created student3_total table before, then also we can create the table and store the data by using single query.

create table student3_total 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

id	name	class	social	science	math	s_id	mark
2	Max Ruin	Three	85	56	85	2	226
3	Arnold	Three	55	40	75	3	170
4	Krish Star	Four	60	50	70	4	180
5	John Mike	Four	60	80	90	5	230
6	Alex John	Four	55	90	80	6	225
7	My John Rob	Fifth	78	60	70	7	208
8	Asruid	Five	85	80	90	8	255
9	Tes Qry	Six	78	60	70	9	208
10	Big John	Four	55	40	55	10	150

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

student3: Download the SQL dump of the above student table

student3_total : Download the SQL dump of total of student mark
SQL Math References Sum query

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

Mukesh	19-01-2019
i have some data , and want to sum Debit and Credit group by account id using select statement not do while or for next statement. i want result in single line statement. ACCOUNT NO. DATE PAYMENT_MODE(Debit/Credit) Amount 000001 01/01/2019 Debit 10000 000001 02/01/2019 Debit 20000 000001 03/01/2019 Credit 15000 000001 03/01/2019 Credit 10000 result Account No Debit Credit 000001 30000 25000

smo1234	19-01-2019
Use Group by to sum based on PAYMENT_MODE(Debit/Credit) Amount. SELECT ACCOUNT_NO, PAYMENT_MODE,SUM(PAYMENT_MODE ) as MODES FROM table_name WHERE ACCOUNT_NO='000001' GROUP BY PAYMENT_MODE Read more on SQL SUM with GROUP Query

Calculating and storing SUM of a column in another table

Creating a table & storing data.

Displaying the result with total mark using two tables

Subhendu Mohapatra