Store Calculated Totals in Another Table

Sometimes a calculated value from one table must be stored in another table. First decide whether you are adding several columns in one row or aggregating several source rows; these are different operations.

Total several columns for each student

When each student is one row, add the subject columns directly; GROUP BY is not required.

INSERT INTO student3_total (s_id, mark)
SELECT id, math + social + science
FROM student3;

Store the average of several columns

INSERT INTO student3_total (s_id, mark)
SELECT id, (math + social + science) / 3
FROM student3;

When SUM() and GROUP BY are appropriate

If marks are stored as multiple exam rows per student, use SUM() with GROUP BY.

INSERT INTO student_total (student_id, total_mark)
SELECT student_id, SUM(mark) AS total_mark
FROM exam_marks
GROUP BY student_id;

Create a new result table

CREATE TABLE ... SELECT can create a result table directly. Alias calculated columns so the new column names are predictable.

CREATE TABLE student3_total AS
SELECT id AS s_id, (math + social + science) AS mark
FROM student3;

Display the stored total with student details

SELECT s.id, s.name, s.class, s.social, s.science, s.math, t.mark AS total_mark
FROM student3 AS s
INNER JOIN student3_total AS t ON t.s_id = s.id
ORDER BY s.id;

student3 SQL dump
student3_total SQL dump

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: