How do I use the SQL sum function to add values in different MySQL tables with the corresponding Primary Key?

Question

I had five different tables created in MySQL containing student records. Each table basically have uniform column names. A StudentID, Subject1_Score, Subject2_Score, Subject3_Score, Total_Score. The five tables are Term1, Term2, Term3, Term4 and overall.

What i want to do is when I insert values into Term1, Term2, Term3 and Term4, the sum of the scores in each column, Subject1_Score, Subject2_Score, Subject3_Score, Total_Score must be automatically calculated and placed in the corresponding columns in overall table. Below is the code i have. I am trying to update overall table when i insert scores into Term1 table. What it is doing at the moment is getting the total sum of the Total_Score column. I want it to take the value of Total_Score in Term1 and add it to the value of Total_Score in overall and then place it inside overall as the updated current value. Below is my code. Please help me achieve what I want to do.

SELECT SUM( Total_Score ) 
FROM (


SELECT SUM( Total_Score ) AS Total_Score
FROM Term1
UNION ALL 
SELECT SUM( Total_Score ) AS Total_Score
FROM overall
) AS ALIAS;

Are you in a position to redesign your db in a more normalised fashion? — P.Salmon, Aug 05 '20 at 07:00
Why do you have 5 identical tables? And see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Aug 05 '20 at 08:13
Fix your data model! Don't try to get a broken data model to work. You should not have one entity spread over five tables. — Gordon Linoff, Aug 05 '20 at 11:45

score 0 · Answer 1 · answered Aug 05 '20 at 09:15

Your current design is not normalised and means a lot of effort is required to achieve what you want.

For EVERY term table create before and after insert trigger,before and after update triggers and an after delete trigger wherein you parse all the tables to calculate the overall.total. Something like this (I have only created insert and update triggers for term1 to cut down on the answer size)

drop trigger if exists trigger_after_term1_insert;
drop trigger if exists trigger_before_term1_insert;
drop trigger if exists trigger_after_term1_update;
drop trigger if exists trigger_before_term1_update;

delimiter $$
create trigger trigger_term1_before_insert before insert on term1
for each row
begin
  insert into debug_table(msg) values (concat('before insert:',new.subject1_score + new.subject2_score + new.subject3_score));
  set new.total = new.subject1_score + new.subject2_score + new.subject3_score; #you may need coalesce here
end $$

delimiter $$
create trigger trigger_term1_before_update before update on term1
for each row
begin
  insert into debug_table(msg) values (concat('before update:',new.subject1_score + new.subject2_score + new.subject3_score));
  set new.total = new.subject1_score + new.subject2_score + new.subject3_score; #you may need coalesce here
end $$

delimiter $$
create trigger trigger_term1_after_insert after insert on term1
for each row
begin
 insert into debug_table(msg) values ('after insert');
 if not exists (select 1 from overall o where o.studentid = new.studentid) then
         insert into debug_table(msg) values ('after insert Not exists');
        insert into overall(studentid,total)
                select studentid,sum(tot) 
                 from
                 (
                 select new.studentid,new.subject1_score + new.subject2_score + new.subject3_score tot
                 union all
                 select studentid,subject1_score + subject2_score + subject3_score from term2 #amend as required
                 union all
                 select studentid,subject1_score + subject2_score + subject3_score from term3 #amend as required
                 union all
                 select studentid,subject1_score + subject2_score + subject3_score from term4 #amend as required
                 ) b
                 group by studentid;
       end if;
end $$

delimiter $$
create trigger trigger_term1_after_update after update on term1
for each row
begin
 insert into debug_table(msg) values ('after update');
 if exists (select 1 from overall o where o.studentid = new.studentid) then
    insert into debug_table(msg) values ('after update exists');
    update overall 
           join (select studentid,sum(tot) tot
                 from
                 (
                 select new.studentid,new.subject1_score + new.subject2_score + new.subject3_score tot
                 union all
                 select studentid,subject1_score + subject2_score + subject3_score from term2 #amend as required
                 union all
                 select studentid,subject1_score + subject2_score + subject3_score from term3 #amend as required
                 union all
                 select studentid,subject1_score + subject2_score + subject3_score from term4 #amend as required
                 ) a
                 group by studentid
                 ) s
                 on s.studentid = overall.studentid
         set overall.total = s.tot;
      else
         insert into debug_table(msg) values ('after update Not exists');
        insert into overall(studentid,total)
                select studentid,sum(tot) 
                 from
                 (
                 select new.studentid,new.subject1_score + new.subject2_score + new.subject3_score tot
                 union all
                 select studentid,subject1_score + subject2_score + subject3_score from term2 #amend as required
                 union all
                 select studentid,subject1_score + subject2_score + subject3_score from term3 #amend as required
                 union all
                 select studentid,subject1_score + subject2_score + subject3_score from term4 #amend as required
                 ) b
                 group by studentid;
       end if;
        
 
end $$
delimiter ;

Note 1) debug_table is there to assist debugging 2) studentid is primary key on all tables

You could dispense with the before triggers if your version of mysql supports generated columns BUT you really should not store data which can be easily calculated.

IF your db was normalised then all this code disappears. Consider a table studentid,term,subject,subject_score

How do I use the SQL sum function to add values in different MySQL tables with the corresponding Primary Key?

1 Answers1