how to make a column that will store sum of all related records from another table in MySQL

I have two tables

episodes

name views fk ---- ----- --- ep 1 234 1----| + -> 234 + 213 ep 2 213 1----| ep 3 634 2----| + -> 634 + 295 ep 4 295 2----|

series

id name views == ---- ----- 1 s 1 sum_of_all_related_views_fields (234+213) 2 s 2 (634+295)

there is a relation between id -> FK == 1 to many

id -> FK == 1

I want to have e column like the above view column what should I do?
all I can think about is views and triggers

1 Answer
1

You could join the series table on an aggregate query from the episodes table:

series

episodes

SELECT s.*, e.views FROM series s JOIN (SELECT fk, SUM(views) AS views FROM episodes GROUP BY fk) e on e.fk = s.id

thanks it worked. can you explain it a little ?

– abdelhamied mostafa
Sep 15 '18 at 15:38

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy