CODEWITHSUNDEEP
mysql
Saruva
Saruva

Reputation: 437

How to update the score column in one table with the average from columns in other three tables?

I have 4 tables that have an score column (10 points max as score)

This structure is similar to the one I have in my real database:

 - master_entity(id, name, **score**, other-columns...)
 - scores_table1(id, score_giver1_id, master_entity_id, **score**)
 - scores_table2(id, score_giver2_id, master_entity_id, **score**)
 - scores_table3(id, score_giver3_id, master_entity_id, **score**)

I would like to execute a simple SQL script (for MySQL database) daily with a cron job. This script must update the master_entity.score column with the average of the averages of the score column in other three tables.

But the average from score column in scores_table1 represent just 1/6 of the average score column value in master_entity.

Also, the average from score column in scores_table2 must represent just 2/6 of the average score column value in master_entity and finally the average from score column in scores_table3 must represent just 3/6 of the average score column value in master_entity

What would be the recommended SQL to update the master_entity.score column using the proportional averages of scores in the other 3 tables?

Upvotes: 0

Views: 122

Answers (1)

Hotdin Gurning
Hotdin Gurning

Reputation: 1819

I have not tried, but based on your question, your sql query should looks like this:

update master_entity m
join(
select master_entity_id,avg(score) as score 
from score_table1
group by master_entity_id
) as s1 on s1.master_entity_id = m.id
join(
select master_entity_id,avg(score) as score 
from score_table2
group by master_entity_id
) as s2 on s2.master_entity_id = m.id
join(
select master_entity_id,avg(score) as score 
from score_table3
group by master_entity_id
) as s3 on s3.master_entity_id = m.id
set m.score = (1/6)*s1.score + (2/6)*s2.score + (3/6)*s3.score

which master_entity.score = (1 /6)*avg(tbl1.score) + (2 /6)*avg(tbl2.score) + (3 /6)*avg(tbl3.score)

Upvotes: 1

Related Questions